1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot.errors import ParseError
  25from sqlglot.helper import (
  26    AutoName,
  27    camel_to_snake_case,
  28    ensure_collection,
  29    seq_get,
  30    split_num_words,
  31    subclasses,
  32)
  33from sqlglot.tokens import Token
  34
  35if t.TYPE_CHECKING:
  36    from sqlglot.dialects.dialect import DialectType
  37
  38E = t.TypeVar("E", bound="Expression")
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55class Expression(metaclass=_Expression):
  56    """
  57    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  58    context, such as its child expressions, their names (arg keys), and whether a given child expression
  59    is optional or not.
  60
  61    Attributes:
  62        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  63            and representing expressions as strings.
  64        arg_types: determines what arguments (child nodes) are supported by an expression. It
  65            maps arg keys to booleans that indicate whether the corresponding args are optional.
  66
  67    Example:
  68        >>> class Foo(Expression):
  69        ...     arg_types = {"this": True, "expression": False}
  70
  71        The above definition informs us that Foo is an Expression that requires an argument called
  72        "this" and may also optionally receive an argument called "expression".
  73
  74    Args:
  75        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  76        parent: a reference to the parent expression (or None, in case of root expressions).
  77        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  78            uses to refer to it.
  79        comments: a list of comments that are associated with a given expression. This is used in
  80            order to preserve comments when transpiling SQL code.
  81        _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  82            optimizer, in order to enable some transformations that require type information.
  83    """
  84
  85    key = "expression"
  86    arg_types = {"this": True}
  87    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta")
  88
  89    def __init__(self, **args: t.Any):
  90        self.args: t.Dict[str, t.Any] = args
  91        self.parent: t.Optional[Expression] = None
  92        self.arg_key: t.Optional[str] = None
  93        self.comments: t.Optional[t.List[str]] = None
  94        self._type: t.Optional[DataType] = None
  95        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  96
  97        for arg_key, value in self.args.items():
  98            self._set_parent(arg_key, value)
  99
 100    def __eq__(self, other) -> bool:
 101        return type(self) is type(other) and _norm_args(self) == _norm_args(other)
 102
 103    def __hash__(self) -> int:
 104        return hash(
 105            (
 106                self.key,
 107                tuple(
 108                    (k, tuple(v) if isinstance(v, list) else v) for k, v in _norm_args(self).items()
 109                ),
 110            )
 111        )
 112
 113    @property
 114    def this(self):
 115        """
 116        Retrieves the argument with key "this".
 117        """
 118        return self.args.get("this")
 119
 120    @property
 121    def expression(self):
 122        """
 123        Retrieves the argument with key "expression".
 124        """
 125        return self.args.get("expression")
 126
 127    @property
 128    def expressions(self):
 129        """
 130        Retrieves the argument with key "expressions".
 131        """
 132        return self.args.get("expressions") or []
 133
 134    def text(self, key) -> str:
 135        """
 136        Returns a textual representation of the argument corresponding to "key". This can only be used
 137        for args that are strings or leaf Expression instances, such as identifiers and literals.
 138        """
 139        field = self.args.get(key)
 140        if isinstance(field, str):
 141            return field
 142        if isinstance(field, (Identifier, Literal, Var)):
 143            return field.this
 144        if isinstance(field, (Star, Null)):
 145            return field.name
 146        return ""
 147
 148    @property
 149    def is_string(self) -> bool:
 150        """
 151        Checks whether a Literal expression is a string.
 152        """
 153        return isinstance(self, Literal) and self.args["is_string"]
 154
 155    @property
 156    def is_number(self) -> bool:
 157        """
 158        Checks whether a Literal expression is a number.
 159        """
 160        return isinstance(self, Literal) and not self.args["is_string"]
 161
 162    @property
 163    def is_int(self) -> bool:
 164        """
 165        Checks whether a Literal expression is an integer.
 166        """
 167        if self.is_number:
 168            try:
 169                int(self.name)
 170                return True
 171            except ValueError:
 172                pass
 173        return False
 174
 175    @property
 176    def is_star(self) -> bool:
 177        """Checks whether an expression is a star."""
 178        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 179
 180    @property
 181    def alias(self) -> str:
 182        """
 183        Returns the alias of the expression, or an empty string if it's not aliased.
 184        """
 185        if isinstance(self.args.get("alias"), TableAlias):
 186            return self.args["alias"].name
 187        return self.text("alias")
 188
 189    @property
 190    def name(self) -> str:
 191        return self.text("this")
 192
 193    @property
 194    def alias_or_name(self):
 195        return self.alias or self.name
 196
 197    @property
 198    def output_name(self):
 199        """
 200        Name of the output column if this expression is a selection.
 201
 202        If the Expression has no output name, an empty string is returned.
 203
 204        Example:
 205            >>> from sqlglot import parse_one
 206            >>> parse_one("SELECT a").expressions[0].output_name
 207            'a'
 208            >>> parse_one("SELECT b AS c").expressions[0].output_name
 209            'c'
 210            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 211            ''
 212        """
 213        return ""
 214
 215    @property
 216    def type(self) -> t.Optional[DataType]:
 217        return self._type
 218
 219    @type.setter
 220    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 221        if dtype and not isinstance(dtype, DataType):
 222            dtype = DataType.build(dtype)
 223        self._type = dtype  # type: ignore
 224
 225    @property
 226    def meta(self) -> t.Dict[str, t.Any]:
 227        if self._meta is None:
 228            self._meta = {}
 229        return self._meta
 230
 231    def __deepcopy__(self, memo):
 232        copy = self.__class__(**deepcopy(self.args))
 233        if self.comments is not None:
 234            copy.comments = deepcopy(self.comments)
 235
 236        if self._type is not None:
 237            copy._type = self._type.copy()
 238
 239        if self._meta is not None:
 240            copy._meta = deepcopy(self._meta)
 241
 242        return copy
 243
 244    def copy(self):
 245        """
 246        Returns a deep copy of the expression.
 247        """
 248        new = deepcopy(self)
 249        new.parent = self.parent
 250        for item, parent, _ in new.bfs():
 251            if isinstance(item, Expression) and parent:
 252                item.parent = parent
 253        return new
 254
 255    def append(self, arg_key, value):
 256        """
 257        Appends value to arg_key if it's a list or sets it as a new list.
 258
 259        Args:
 260            arg_key (str): name of the list expression arg
 261            value (Any): value to append to the list
 262        """
 263        if not isinstance(self.args.get(arg_key), list):
 264            self.args[arg_key] = []
 265        self.args[arg_key].append(value)
 266        self._set_parent(arg_key, value)
 267
 268    def set(self, arg_key, value):
 269        """
 270        Sets `arg_key` to `value`.
 271
 272        Args:
 273            arg_key (str): name of the expression arg.
 274            value: value to set the arg to.
 275        """
 276        self.args[arg_key] = value
 277        self._set_parent(arg_key, value)
 278
 279    def _set_parent(self, arg_key, value):
 280        if isinstance(value, Expression):
 281            value.parent = self
 282            value.arg_key = arg_key
 283        elif isinstance(value, list):
 284            for v in value:
 285                if isinstance(v, Expression):
 286                    v.parent = self
 287                    v.arg_key = arg_key
 288
 289    @property
 290    def depth(self):
 291        """
 292        Returns the depth of this tree.
 293        """
 294        if self.parent:
 295            return self.parent.depth + 1
 296        return 0
 297
 298    def find(self, *expression_types: t.Type[E], bfs=True) -> E | None:
 299        """
 300        Returns the first node in this tree which matches at least one of
 301        the specified types.
 302
 303        Args:
 304            expression_types (type): the expression type(s) to match.
 305
 306        Returns:
 307            The node which matches the criteria or None if no such node was found.
 308        """
 309        return next(self.find_all(*expression_types, bfs=bfs), None)
 310
 311    def find_all(self, *expression_types: t.Type[E], bfs=True) -> t.Iterator[E]:
 312        """
 313        Returns a generator object which visits all nodes in this tree and only
 314        yields those that match at least one of the specified expression types.
 315
 316        Args:
 317            expression_types (type): the expression type(s) to match.
 318
 319        Returns:
 320            The generator object.
 321        """
 322        for expression, _, _ in self.walk(bfs=bfs):
 323            if isinstance(expression, expression_types):
 324                yield expression
 325
 326    def find_ancestor(self, *expression_types: t.Type[E]) -> E | None:
 327        """
 328        Returns a nearest parent matching expression_types.
 329
 330        Args:
 331            expression_types (type): the expression type(s) to match.
 332
 333        Returns:
 334            The parent node.
 335        """
 336        ancestor = self.parent
 337        while ancestor and not isinstance(ancestor, expression_types):
 338            ancestor = ancestor.parent
 339        # ignore type because mypy doesn't know that we're checking type in the loop
 340        return ancestor  # type: ignore[return-value]
 341
 342    @property
 343    def parent_select(self):
 344        """
 345        Returns the parent select statement.
 346        """
 347        return self.find_ancestor(Select)
 348
 349    def root(self) -> Expression:
 350        """
 351        Returns the root expression of this tree.
 352        """
 353        expression = self
 354        while expression.parent:
 355            expression = expression.parent
 356        return expression
 357
 358    def walk(self, bfs=True, prune=None):
 359        """
 360        Returns a generator object which visits all nodes in this tree.
 361
 362        Args:
 363            bfs (bool): if set to True the BFS traversal order will be applied,
 364                otherwise the DFS traversal will be used instead.
 365            prune ((node, parent, arg_key) -> bool): callable that returns True if
 366                the generator should stop traversing this branch of the tree.
 367
 368        Returns:
 369            the generator object.
 370        """
 371        if bfs:
 372            yield from self.bfs(prune=prune)
 373        else:
 374            yield from self.dfs(prune=prune)
 375
 376    def dfs(self, parent=None, key=None, prune=None):
 377        """
 378        Returns a generator object which visits all nodes in this tree in
 379        the DFS (Depth-first) order.
 380
 381        Returns:
 382            The generator object.
 383        """
 384        parent = parent or self.parent
 385        yield self, parent, key
 386        if prune and prune(self, parent, key):
 387            return
 388
 389        for k, v in self.args.items():
 390            for node in ensure_collection(v):
 391                if isinstance(node, Expression):
 392                    yield from node.dfs(self, k, prune)
 393
 394    def bfs(self, prune=None):
 395        """
 396        Returns a generator object which visits all nodes in this tree in
 397        the BFS (Breadth-first) order.
 398
 399        Returns:
 400            The generator object.
 401        """
 402        queue = deque([(self, self.parent, None)])
 403
 404        while queue:
 405            item, parent, key = queue.popleft()
 406
 407            yield item, parent, key
 408            if prune and prune(item, parent, key):
 409                continue
 410
 411            if isinstance(item, Expression):
 412                for k, v in item.args.items():
 413                    for node in ensure_collection(v):
 414                        if isinstance(node, Expression):
 415                            queue.append((node, item, k))
 416
 417    def unnest(self):
 418        """
 419        Returns the first non parenthesis child or self.
 420        """
 421        expression = self
 422        while isinstance(expression, Paren):
 423            expression = expression.this
 424        return expression
 425
 426    def unalias(self):
 427        """
 428        Returns the inner expression if this is an Alias.
 429        """
 430        if isinstance(self, Alias):
 431            return self.this
 432        return self
 433
 434    def unnest_operands(self):
 435        """
 436        Returns unnested operands as a tuple.
 437        """
 438        return tuple(arg.unnest() for arg in self.args.values() if arg)
 439
 440    def flatten(self, unnest=True):
 441        """
 442        Returns a generator which yields child nodes who's parents are the same class.
 443
 444        A AND B AND C -> [A, B, C]
 445        """
 446        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not isinstance(n, self.__class__)):
 447            if not isinstance(node, self.__class__):
 448                yield node.unnest() if unnest else node
 449
 450    def __str__(self):
 451        return self.sql()
 452
 453    def __repr__(self):
 454        return self._to_s()
 455
 456    def sql(self, dialect: DialectType = None, **opts) -> str:
 457        """
 458        Returns SQL string representation of this tree.
 459
 460        Args:
 461            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 462            opts: other `sqlglot.generator.Generator` options.
 463
 464        Returns:
 465            The SQL string.
 466        """
 467        from sqlglot.dialects import Dialect
 468
 469        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 470
 471    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 472        indent = "" if not level else "\n"
 473        indent += "".join(["  "] * level)
 474        left = f"({self.key.upper()} "
 475
 476        args: t.Dict[str, t.Any] = {
 477            k: ", ".join(
 478                v._to_s(hide_missing=hide_missing, level=level + 1)
 479                if hasattr(v, "_to_s")
 480                else str(v)
 481                for v in ensure_collection(vs)
 482                if v is not None
 483            )
 484            for k, vs in self.args.items()
 485        }
 486        args["comments"] = self.comments
 487        args["type"] = self.type
 488        args = {k: v for k, v in args.items() if v or not hide_missing}
 489
 490        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 491        right += ")"
 492
 493        return indent + left + right
 494
 495    def transform(self, fun, *args, copy=True, **kwargs):
 496        """
 497        Recursively visits all tree nodes (excluding already transformed ones)
 498        and applies the given transformation function to each node.
 499
 500        Args:
 501            fun (function): a function which takes a node as an argument and returns a
 502                new transformed node or the same node without modifications. If the function
 503                returns None, then the corresponding node will be removed from the syntax tree.
 504            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 505                modified in place.
 506
 507        Returns:
 508            The transformed tree.
 509        """
 510        node = self.copy() if copy else self
 511        new_node = fun(node, *args, **kwargs)
 512
 513        if new_node is None or not isinstance(new_node, Expression):
 514            return new_node
 515        if new_node is not node:
 516            new_node.parent = node.parent
 517            return new_node
 518
 519        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 520        return new_node
 521
 522    def replace(self, expression):
 523        """
 524        Swap out this expression with a new expression.
 525
 526        For example::
 527
 528            >>> tree = Select().select("x").from_("tbl")
 529            >>> tree.find(Column).replace(Column(this="y"))
 530            (COLUMN this: y)
 531            >>> tree.sql()
 532            'SELECT y FROM tbl'
 533
 534        Args:
 535            expression (Expression|None): new node
 536
 537        Returns:
 538            The new expression or expressions.
 539        """
 540        if not self.parent:
 541            return expression
 542
 543        parent = self.parent
 544        self.parent = None
 545
 546        replace_children(parent, lambda child: expression if child is self else child)
 547        return expression
 548
 549    def pop(self):
 550        """
 551        Remove this expression from its AST.
 552        """
 553        self.replace(None)
 554
 555    def assert_is(self, type_):
 556        """
 557        Assert that this `Expression` is an instance of `type_`.
 558
 559        If it is NOT an instance of `type_`, this raises an assertion error.
 560        Otherwise, this returns this expression.
 561
 562        Examples:
 563            This is useful for type security in chained expressions:
 564
 565            >>> import sqlglot
 566            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 567            'SELECT x, z FROM y'
 568        """
 569        assert isinstance(self, type_)
 570        return self
 571
 572    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 573        """
 574        Checks if this expression is valid (e.g. all mandatory args are set).
 575
 576        Args:
 577            args: a sequence of values that were used to instantiate a Func expression. This is used
 578                to check that the provided arguments don't exceed the function argument limit.
 579
 580        Returns:
 581            A list of error messages for all possible errors that were found.
 582        """
 583        errors: t.List[str] = []
 584
 585        for k in self.args:
 586            if k not in self.arg_types:
 587                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 588        for k, mandatory in self.arg_types.items():
 589            v = self.args.get(k)
 590            if mandatory and (v is None or (isinstance(v, list) and not v)):
 591                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 592
 593        if (
 594            args
 595            and isinstance(self, Func)
 596            and len(args) > len(self.arg_types)
 597            and not self.is_var_len_args
 598        ):
 599            errors.append(
 600                f"The number of provided arguments ({len(args)}) is greater than "
 601                f"the maximum number of supported arguments ({len(self.arg_types)})"
 602            )
 603
 604        return errors
 605
 606    def dump(self):
 607        """
 608        Dump this Expression to a JSON-serializable dict.
 609        """
 610        from sqlglot.serde import dump
 611
 612        return dump(self)
 613
 614    @classmethod
 615    def load(cls, obj):
 616        """
 617        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 618        """
 619        from sqlglot.serde import load
 620
 621        return load(obj)
 622
 623
 624IntoType = t.Union[
 625    str,
 626    t.Type[Expression],
 627    t.Collection[t.Union[str, t.Type[Expression]]],
 628]
 629
 630
 631class Condition(Expression):
 632    def and_(self, *expressions, dialect=None, **opts):
 633        """
 634        AND this condition with one or multiple expressions.
 635
 636        Example:
 637            >>> condition("x=1").and_("y=1").sql()
 638            'x = 1 AND y = 1'
 639
 640        Args:
 641            *expressions (str | Expression): the SQL code strings to parse.
 642                If an `Expression` instance is passed, it will be used as-is.
 643            dialect (str): the dialect used to parse the input expression.
 644            opts (kwargs): other options to use to parse the input expressions.
 645
 646        Returns:
 647            And: the new condition.
 648        """
 649        return and_(self, *expressions, dialect=dialect, **opts)
 650
 651    def or_(self, *expressions, dialect=None, **opts):
 652        """
 653        OR this condition with one or multiple expressions.
 654
 655        Example:
 656            >>> condition("x=1").or_("y=1").sql()
 657            'x = 1 OR y = 1'
 658
 659        Args:
 660            *expressions (str | Expression): the SQL code strings to parse.
 661                If an `Expression` instance is passed, it will be used as-is.
 662            dialect (str): the dialect used to parse the input expression.
 663            opts (kwargs): other options to use to parse the input expressions.
 664
 665        Returns:
 666            Or: the new condition.
 667        """
 668        return or_(self, *expressions, dialect=dialect, **opts)
 669
 670    def not_(self):
 671        """
 672        Wrap this condition with NOT.
 673
 674        Example:
 675            >>> condition("x=1").not_().sql()
 676            'NOT x = 1'
 677
 678        Returns:
 679            Not: the new condition.
 680        """
 681        return not_(self)
 682
 683
 684class Predicate(Condition):
 685    """Relationships like x = y, x > 1, x >= y."""
 686
 687
 688class DerivedTable(Expression):
 689    @property
 690    def alias_column_names(self):
 691        table_alias = self.args.get("alias")
 692        if not table_alias:
 693            return []
 694        column_list = table_alias.assert_is(TableAlias).args.get("columns") or []
 695        return [c.name for c in column_list]
 696
 697    @property
 698    def selects(self):
 699        alias = self.args.get("alias")
 700
 701        if alias:
 702            return alias.columns
 703        return []
 704
 705    @property
 706    def named_selects(self):
 707        return [select.output_name for select in self.selects]
 708
 709
 710class Unionable(Expression):
 711    def union(self, expression, distinct=True, dialect=None, **opts):
 712        """
 713        Builds a UNION expression.
 714
 715        Example:
 716            >>> import sqlglot
 717            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 718            'SELECT * FROM foo UNION SELECT * FROM bla'
 719
 720        Args:
 721            expression (str | Expression): the SQL code string.
 722                If an `Expression` instance is passed, it will be used as-is.
 723            distinct (bool): set the DISTINCT flag if and only if this is true.
 724            dialect (str): the dialect used to parse the input expression.
 725            opts (kwargs): other options to use to parse the input expressions.
 726        Returns:
 727            Union: the Union expression.
 728        """
 729        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 730
 731    def intersect(self, expression, distinct=True, dialect=None, **opts):
 732        """
 733        Builds an INTERSECT expression.
 734
 735        Example:
 736            >>> import sqlglot
 737            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 738            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 739
 740        Args:
 741            expression (str | Expression): the SQL code string.
 742                If an `Expression` instance is passed, it will be used as-is.
 743            distinct (bool): set the DISTINCT flag if and only if this is true.
 744            dialect (str): the dialect used to parse the input expression.
 745            opts (kwargs): other options to use to parse the input expressions.
 746        Returns:
 747            Intersect: the Intersect expression
 748        """
 749        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 750
 751    def except_(self, expression, distinct=True, dialect=None, **opts):
 752        """
 753        Builds an EXCEPT expression.
 754
 755        Example:
 756            >>> import sqlglot
 757            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 758            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 759
 760        Args:
 761            expression (str | Expression): the SQL code string.
 762                If an `Expression` instance is passed, it will be used as-is.
 763            distinct (bool): set the DISTINCT flag if and only if this is true.
 764            dialect (str): the dialect used to parse the input expression.
 765            opts (kwargs): other options to use to parse the input expressions.
 766        Returns:
 767            Except: the Except expression
 768        """
 769        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 770
 771
 772class UDTF(DerivedTable, Unionable):
 773    pass
 774
 775
 776class Cache(Expression):
 777    arg_types = {
 778        "with": False,
 779        "this": True,
 780        "lazy": False,
 781        "options": False,
 782        "expression": False,
 783    }
 784
 785
 786class Uncache(Expression):
 787    arg_types = {"this": True, "exists": False}
 788
 789
 790class Create(Expression):
 791    arg_types = {
 792        "with": False,
 793        "this": True,
 794        "kind": True,
 795        "expression": False,
 796        "exists": False,
 797        "properties": False,
 798        "replace": False,
 799        "unique": False,
 800        "volatile": False,
 801        "indexes": False,
 802        "no_schema_binding": False,
 803        "begin": False,
 804    }
 805
 806
 807class Describe(Expression):
 808    arg_types = {"this": True, "kind": False}
 809
 810
 811class Set(Expression):
 812    arg_types = {"expressions": True}
 813
 814
 815class SetItem(Expression):
 816    arg_types = {
 817        "this": False,
 818        "expressions": False,
 819        "kind": False,
 820        "collate": False,  # MySQL SET NAMES statement
 821        "global": False,
 822    }
 823
 824
 825class Show(Expression):
 826    arg_types = {
 827        "this": True,
 828        "target": False,
 829        "offset": False,
 830        "limit": False,
 831        "like": False,
 832        "where": False,
 833        "db": False,
 834        "full": False,
 835        "mutex": False,
 836        "query": False,
 837        "channel": False,
 838        "global": False,
 839        "log": False,
 840        "position": False,
 841        "types": False,
 842    }
 843
 844
 845class UserDefinedFunction(Expression):
 846    arg_types = {"this": True, "expressions": False, "wrapped": False}
 847
 848
 849class CharacterSet(Expression):
 850    arg_types = {"this": True, "default": False}
 851
 852
 853class With(Expression):
 854    arg_types = {"expressions": True, "recursive": False}
 855
 856    @property
 857    def recursive(self) -> bool:
 858        return bool(self.args.get("recursive"))
 859
 860
 861class WithinGroup(Expression):
 862    arg_types = {"this": True, "expression": False}
 863
 864
 865class CTE(DerivedTable):
 866    arg_types = {"this": True, "alias": True}
 867
 868
 869class TableAlias(Expression):
 870    arg_types = {"this": False, "columns": False}
 871
 872    @property
 873    def columns(self):
 874        return self.args.get("columns") or []
 875
 876
 877class BitString(Condition):
 878    pass
 879
 880
 881class HexString(Condition):
 882    pass
 883
 884
 885class ByteString(Condition):
 886    pass
 887
 888
 889class Column(Condition):
 890    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
 891
 892    @property
 893    def table(self) -> str:
 894        return self.text("table")
 895
 896    @property
 897    def db(self) -> str:
 898        return self.text("db")
 899
 900    @property
 901    def catalog(self) -> str:
 902        return self.text("catalog")
 903
 904    @property
 905    def output_name(self) -> str:
 906        return self.name
 907
 908
 909class ColumnDef(Expression):
 910    arg_types = {
 911        "this": True,
 912        "kind": False,
 913        "constraints": False,
 914        "exists": False,
 915    }
 916
 917
 918class AlterColumn(Expression):
 919    arg_types = {
 920        "this": True,
 921        "dtype": False,
 922        "collate": False,
 923        "using": False,
 924        "default": False,
 925        "drop": False,
 926    }
 927
 928
 929class RenameTable(Expression):
 930    pass
 931
 932
 933class SetTag(Expression):
 934    arg_types = {"expressions": True, "unset": False}
 935
 936
 937class ColumnConstraint(Expression):
 938    arg_types = {"this": False, "kind": True}
 939
 940
 941class ColumnConstraintKind(Expression):
 942    pass
 943
 944
 945class AutoIncrementColumnConstraint(ColumnConstraintKind):
 946    pass
 947
 948
 949class CaseSpecificColumnConstraint(ColumnConstraintKind):
 950    arg_types = {"not_": True}
 951
 952
 953class CharacterSetColumnConstraint(ColumnConstraintKind):
 954    arg_types = {"this": True}
 955
 956
 957class CheckColumnConstraint(ColumnConstraintKind):
 958    pass
 959
 960
 961class CollateColumnConstraint(ColumnConstraintKind):
 962    pass
 963
 964
 965class CommentColumnConstraint(ColumnConstraintKind):
 966    pass
 967
 968
 969class CompressColumnConstraint(ColumnConstraintKind):
 970    pass
 971
 972
 973class DateFormatColumnConstraint(ColumnConstraintKind):
 974    arg_types = {"this": True}
 975
 976
 977class DefaultColumnConstraint(ColumnConstraintKind):
 978    pass
 979
 980
 981class EncodeColumnConstraint(ColumnConstraintKind):
 982    pass
 983
 984
 985class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
 986    # this: True -> ALWAYS, this: False -> BY DEFAULT
 987    arg_types = {
 988        "this": False,
 989        "start": False,
 990        "increment": False,
 991        "minvalue": False,
 992        "maxvalue": False,
 993        "cycle": False,
 994    }
 995
 996
 997class InlineLengthColumnConstraint(ColumnConstraintKind):
 998    pass
 999
1000
1001class NotNullColumnConstraint(ColumnConstraintKind):
1002    arg_types = {"allow_null": False}
1003
1004
1005class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1006    arg_types = {"desc": False}
1007
1008
1009class TitleColumnConstraint(ColumnConstraintKind):
1010    pass
1011
1012
1013class UniqueColumnConstraint(ColumnConstraintKind):
1014    arg_types: t.Dict[str, t.Any] = {}
1015
1016
1017class UppercaseColumnConstraint(ColumnConstraintKind):
1018    arg_types: t.Dict[str, t.Any] = {}
1019
1020
1021class PathColumnConstraint(ColumnConstraintKind):
1022    pass
1023
1024
1025class Constraint(Expression):
1026    arg_types = {"this": True, "expressions": True}
1027
1028
1029class Delete(Expression):
1030    arg_types = {"with": False, "this": False, "using": False, "where": False}
1031
1032
1033class Drop(Expression):
1034    arg_types = {
1035        "this": False,
1036        "kind": False,
1037        "exists": False,
1038        "temporary": False,
1039        "materialized": False,
1040        "cascade": False,
1041    }
1042
1043
1044class Filter(Expression):
1045    arg_types = {"this": True, "expression": True}
1046
1047
1048class Check(Expression):
1049    pass
1050
1051
1052class Directory(Expression):
1053    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1054    arg_types = {"this": True, "local": False, "row_format": False}
1055
1056
1057class ForeignKey(Expression):
1058    arg_types = {
1059        "expressions": True,
1060        "reference": False,
1061        "delete": False,
1062        "update": False,
1063    }
1064
1065
1066class PrimaryKey(Expression):
1067    arg_types = {"expressions": True, "options": False}
1068
1069
1070class Unique(Expression):
1071    arg_types = {"expressions": True}
1072
1073
1074# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1075# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1076class Into(Expression):
1077    arg_types = {"this": True, "temporary": False, "unlogged": False}
1078
1079
1080class From(Expression):
1081    arg_types = {"expressions": True}
1082
1083
1084class Having(Expression):
1085    pass
1086
1087
1088class Hint(Expression):
1089    arg_types = {"expressions": True}
1090
1091
1092class JoinHint(Expression):
1093    arg_types = {"this": True, "expressions": True}
1094
1095
1096class Identifier(Expression):
1097    arg_types = {"this": True, "quoted": False}
1098
1099    @property
1100    def quoted(self):
1101        return bool(self.args.get("quoted"))
1102
1103    def __eq__(self, other):
1104        return isinstance(other, self.__class__) and _norm_arg(self.this) == _norm_arg(other.this)
1105
1106    def __hash__(self):
1107        return hash((self.key, self.this.lower()))
1108
1109    @property
1110    def output_name(self):
1111        return self.name
1112
1113
1114class Index(Expression):
1115    arg_types = {
1116        "this": False,
1117        "table": False,
1118        "where": False,
1119        "columns": False,
1120        "unique": False,
1121        "primary": False,
1122        "amp": False,  # teradata
1123    }
1124
1125
1126class Insert(Expression):
1127    arg_types = {
1128        "with": False,
1129        "this": True,
1130        "expression": False,
1131        "overwrite": False,
1132        "exists": False,
1133        "partition": False,
1134        "alternative": False,
1135    }
1136
1137
1138# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1139class Introducer(Expression):
1140    arg_types = {"this": True, "expression": True}
1141
1142
1143# national char, like n'utf8'
1144class National(Expression):
1145    pass
1146
1147
1148class LoadData(Expression):
1149    arg_types = {
1150        "this": True,
1151        "local": False,
1152        "overwrite": False,
1153        "inpath": True,
1154        "partition": False,
1155        "input_format": False,
1156        "serde": False,
1157    }
1158
1159
1160class Partition(Expression):
1161    arg_types = {"expressions": True}
1162
1163
1164class Fetch(Expression):
1165    arg_types = {"direction": False, "count": False}
1166
1167
1168class Group(Expression):
1169    arg_types = {
1170        "expressions": False,
1171        "grouping_sets": False,
1172        "cube": False,
1173        "rollup": False,
1174    }
1175
1176
1177class Lambda(Expression):
1178    arg_types = {"this": True, "expressions": True}
1179
1180
1181class Limit(Expression):
1182    arg_types = {"this": False, "expression": True}
1183
1184
1185class Literal(Condition):
1186    arg_types = {"this": True, "is_string": True}
1187
1188    def __eq__(self, other):
1189        return (
1190            isinstance(other, Literal)
1191            and self.this == other.this
1192            and self.args["is_string"] == other.args["is_string"]
1193        )
1194
1195    def __hash__(self):
1196        return hash((self.key, self.this, self.args["is_string"]))
1197
1198    @classmethod
1199    def number(cls, number) -> Literal:
1200        return cls(this=str(number), is_string=False)
1201
1202    @classmethod
1203    def string(cls, string) -> Literal:
1204        return cls(this=str(string), is_string=True)
1205
1206    @property
1207    def output_name(self):
1208        return self.name
1209
1210
1211class Join(Expression):
1212    arg_types = {
1213        "this": True,
1214        "on": False,
1215        "side": False,
1216        "kind": False,
1217        "using": False,
1218        "natural": False,
1219    }
1220
1221    @property
1222    def kind(self):
1223        return self.text("kind").upper()
1224
1225    @property
1226    def side(self):
1227        return self.text("side").upper()
1228
1229    @property
1230    def alias_or_name(self):
1231        return self.this.alias_or_name
1232
1233    def on(self, *expressions, append=True, dialect=None, copy=True, **opts):
1234        """
1235        Append to or set the ON expressions.
1236
1237        Example:
1238            >>> import sqlglot
1239            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1240            'JOIN x ON y = 1'
1241
1242        Args:
1243            *expressions (str | Expression): the SQL code strings to parse.
1244                If an `Expression` instance is passed, it will be used as-is.
1245                Multiple expressions are combined with an AND operator.
1246            append (bool): if `True`, AND the new expressions to any existing expression.
1247                Otherwise, this resets the expression.
1248            dialect (str): the dialect used to parse the input expressions.
1249            copy (bool): if `False`, modify this expression instance in-place.
1250            opts (kwargs): other options to use to parse the input expressions.
1251
1252        Returns:
1253            Join: the modified join expression.
1254        """
1255        join = _apply_conjunction_builder(
1256            *expressions,
1257            instance=self,
1258            arg="on",
1259            append=append,
1260            dialect=dialect,
1261            copy=copy,
1262            **opts,
1263        )
1264
1265        if join.kind == "CROSS":
1266            join.set("kind", None)
1267
1268        return join
1269
1270    def using(self, *expressions, append=True, dialect=None, copy=True, **opts):
1271        """
1272        Append to or set the USING expressions.
1273
1274        Example:
1275            >>> import sqlglot
1276            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1277            'JOIN x USING (foo, bla)'
1278
1279        Args:
1280            *expressions (str | Expression): the SQL code strings to parse.
1281                If an `Expression` instance is passed, it will be used as-is.
1282            append (bool): if `True`, concatenate the new expressions to the existing "using" list.
1283                Otherwise, this resets the expression.
1284            dialect (str): the dialect used to parse the input expressions.
1285            copy (bool): if `False`, modify this expression instance in-place.
1286            opts (kwargs): other options to use to parse the input expressions.
1287
1288        Returns:
1289            Join: the modified join expression.
1290        """
1291        join = _apply_list_builder(
1292            *expressions,
1293            instance=self,
1294            arg="using",
1295            append=append,
1296            dialect=dialect,
1297            copy=copy,
1298            **opts,
1299        )
1300
1301        if join.kind == "CROSS":
1302            join.set("kind", None)
1303
1304        return join
1305
1306
1307class Lateral(UDTF):
1308    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1309
1310
1311class MatchRecognize(Expression):
1312    arg_types = {
1313        "partition_by": False,
1314        "order": False,
1315        "measures": False,
1316        "rows": False,
1317        "after": False,
1318        "pattern": False,
1319        "define": False,
1320    }
1321
1322
1323# Clickhouse FROM FINAL modifier
1324# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1325class Final(Expression):
1326    pass
1327
1328
1329class Offset(Expression):
1330    arg_types = {"this": False, "expression": True}
1331
1332
1333class Order(Expression):
1334    arg_types = {"this": False, "expressions": True}
1335
1336
1337# hive specific sorts
1338# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1339class Cluster(Order):
1340    pass
1341
1342
1343class Distribute(Order):
1344    pass
1345
1346
1347class Sort(Order):
1348    pass
1349
1350
1351class Ordered(Expression):
1352    arg_types = {"this": True, "desc": True, "nulls_first": True}
1353
1354
1355class Property(Expression):
1356    arg_types = {"this": True, "value": True}
1357
1358
1359class AfterJournalProperty(Property):
1360    arg_types = {"no": True, "dual": False, "local": False}
1361
1362
1363class AlgorithmProperty(Property):
1364    arg_types = {"this": True}
1365
1366
1367class AutoIncrementProperty(Property):
1368    arg_types = {"this": True}
1369
1370
1371class BlockCompressionProperty(Property):
1372    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1373
1374
1375class CharacterSetProperty(Property):
1376    arg_types = {"this": True, "default": True}
1377
1378
1379class ChecksumProperty(Property):
1380    arg_types = {"on": False, "default": False}
1381
1382
1383class CollateProperty(Property):
1384    arg_types = {"this": True}
1385
1386
1387class DataBlocksizeProperty(Property):
1388    arg_types = {"size": False, "units": False, "min": False, "default": False}
1389
1390
1391class DefinerProperty(Property):
1392    arg_types = {"this": True}
1393
1394
1395class DistKeyProperty(Property):
1396    arg_types = {"this": True}
1397
1398
1399class DistStyleProperty(Property):
1400    arg_types = {"this": True}
1401
1402
1403class EngineProperty(Property):
1404    arg_types = {"this": True}
1405
1406
1407class ExecuteAsProperty(Property):
1408    arg_types = {"this": True}
1409
1410
1411class ExternalProperty(Property):
1412    arg_types = {"this": False}
1413
1414
1415class FallbackProperty(Property):
1416    arg_types = {"no": True, "protection": False}
1417
1418
1419class FileFormatProperty(Property):
1420    arg_types = {"this": True}
1421
1422
1423class FreespaceProperty(Property):
1424    arg_types = {"this": True, "percent": False}
1425
1426
1427class IsolatedLoadingProperty(Property):
1428    arg_types = {
1429        "no": True,
1430        "concurrent": True,
1431        "for_all": True,
1432        "for_insert": True,
1433        "for_none": True,
1434    }
1435
1436
1437class JournalProperty(Property):
1438    arg_types = {"no": True, "dual": False, "before": False}
1439
1440
1441class LanguageProperty(Property):
1442    arg_types = {"this": True}
1443
1444
1445class LikeProperty(Property):
1446    arg_types = {"this": True, "expressions": False}
1447
1448
1449class LocationProperty(Property):
1450    arg_types = {"this": True}
1451
1452
1453class LockingProperty(Property):
1454    arg_types = {
1455        "this": False,
1456        "kind": True,
1457        "for_or_in": True,
1458        "lock_type": True,
1459        "override": False,
1460    }
1461
1462
1463class LogProperty(Property):
1464    arg_types = {"no": True}
1465
1466
1467class MaterializedProperty(Property):
1468    arg_types = {"this": False}
1469
1470
1471class MergeBlockRatioProperty(Property):
1472    arg_types = {"this": False, "no": False, "default": False, "percent": False}
1473
1474
1475class NoPrimaryIndexProperty(Property):
1476    arg_types = {"this": False}
1477
1478
1479class OnCommitProperty(Property):
1480    arg_type = {"this": False}
1481
1482
1483class PartitionedByProperty(Property):
1484    arg_types = {"this": True}
1485
1486
1487class ReturnsProperty(Property):
1488    arg_types = {"this": True, "is_table": False, "table": False}
1489
1490
1491class RowFormatDelimitedProperty(Property):
1492    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
1493    arg_types = {
1494        "fields": False,
1495        "escaped": False,
1496        "collection_items": False,
1497        "map_keys": False,
1498        "lines": False,
1499        "null": False,
1500        "serde": False,
1501    }
1502
1503
1504class RowFormatSerdeProperty(Property):
1505    arg_types = {"this": True}
1506
1507
1508class SchemaCommentProperty(Property):
1509    arg_types = {"this": True}
1510
1511
1512class SerdeProperties(Property):
1513    arg_types = {"expressions": True}
1514
1515
1516class SetProperty(Property):
1517    arg_types = {"multi": True}
1518
1519
1520class SortKeyProperty(Property):
1521    arg_types = {"this": True, "compound": False}
1522
1523
1524class SqlSecurityProperty(Property):
1525    arg_types = {"definer": True}
1526
1527
1528class TableFormatProperty(Property):
1529    arg_types = {"this": True}
1530
1531
1532class TemporaryProperty(Property):
1533    arg_types = {"global_": True}
1534
1535
1536class TransientProperty(Property):
1537    arg_types = {"this": False}
1538
1539
1540class VolatilityProperty(Property):
1541    arg_types = {"this": True}
1542
1543
1544class WithDataProperty(Property):
1545    arg_types = {"no": True, "statistics": False}
1546
1547
1548class WithJournalTableProperty(Property):
1549    arg_types = {"this": True}
1550
1551
1552class Properties(Expression):
1553    arg_types = {"expressions": True}
1554
1555    NAME_TO_PROPERTY = {
1556        "ALGORITHM": AlgorithmProperty,
1557        "AUTO_INCREMENT": AutoIncrementProperty,
1558        "CHARACTER SET": CharacterSetProperty,
1559        "COLLATE": CollateProperty,
1560        "COMMENT": SchemaCommentProperty,
1561        "DEFINER": DefinerProperty,
1562        "DISTKEY": DistKeyProperty,
1563        "DISTSTYLE": DistStyleProperty,
1564        "ENGINE": EngineProperty,
1565        "EXECUTE AS": ExecuteAsProperty,
1566        "FORMAT": FileFormatProperty,
1567        "LANGUAGE": LanguageProperty,
1568        "LOCATION": LocationProperty,
1569        "PARTITIONED_BY": PartitionedByProperty,
1570        "RETURNS": ReturnsProperty,
1571        "SORTKEY": SortKeyProperty,
1572        "TABLE_FORMAT": TableFormatProperty,
1573    }
1574
1575    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
1576
1577    # CREATE property locations
1578    # Form: schema specified
1579    #   create [POST_CREATE]
1580    #     table a [POST_NAME]
1581    #     (b int) [POST_SCHEMA]
1582    #     with ([POST_WITH])
1583    #     index (b) [POST_INDEX]
1584    #
1585    # Form: alias selection
1586    #   create [POST_CREATE]
1587    #     table a [POST_NAME]
1588    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
1589    #     index (c) [POST_INDEX]
1590    class Location(AutoName):
1591        POST_CREATE = auto()
1592        POST_NAME = auto()
1593        POST_SCHEMA = auto()
1594        POST_WITH = auto()
1595        POST_ALIAS = auto()
1596        POST_EXPRESSION = auto()
1597        POST_INDEX = auto()
1598        UNSUPPORTED = auto()
1599
1600    @classmethod
1601    def from_dict(cls, properties_dict) -> Properties:
1602        expressions = []
1603        for key, value in properties_dict.items():
1604            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
1605            if property_cls:
1606                expressions.append(property_cls(this=convert(value)))
1607            else:
1608                expressions.append(Property(this=Literal.string(key), value=convert(value)))
1609
1610        return cls(expressions=expressions)
1611
1612
1613class Qualify(Expression):
1614    pass
1615
1616
1617# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
1618class Return(Expression):
1619    pass
1620
1621
1622class Reference(Expression):
1623    arg_types = {"this": True, "expressions": False, "options": False}
1624
1625
1626class Tuple(Expression):
1627    arg_types = {"expressions": False}
1628
1629
1630class Subqueryable(Unionable):
1631    def subquery(self, alias=None, copy=True) -> Subquery:
1632        """
1633        Convert this expression to an aliased expression that can be used as a Subquery.
1634
1635        Example:
1636            >>> subquery = Select().select("x").from_("tbl").subquery()
1637            >>> Select().select("x").from_(subquery).sql()
1638            'SELECT x FROM (SELECT x FROM tbl)'
1639
1640        Args:
1641            alias (str | Identifier): an optional alias for the subquery
1642            copy (bool): if `False`, modify this expression instance in-place.
1643
1644        Returns:
1645            Alias: the subquery
1646        """
1647        instance = _maybe_copy(self, copy)
1648        return Subquery(
1649            this=instance,
1650            alias=TableAlias(this=to_identifier(alias)),
1651        )
1652
1653    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1654        raise NotImplementedError
1655
1656    @property
1657    def ctes(self):
1658        with_ = self.args.get("with")
1659        if not with_:
1660            return []
1661        return with_.expressions
1662
1663    @property
1664    def selects(self):
1665        raise NotImplementedError("Subqueryable objects must implement `selects`")
1666
1667    @property
1668    def named_selects(self):
1669        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
1670
1671    def with_(
1672        self,
1673        alias,
1674        as_,
1675        recursive=None,
1676        append=True,
1677        dialect=None,
1678        copy=True,
1679        **opts,
1680    ):
1681        """
1682        Append to or set the common table expressions.
1683
1684        Example:
1685            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1686            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1687
1688        Args:
1689            alias (str | Expression): the SQL code string to parse as the table name.
1690                If an `Expression` instance is passed, this is used as-is.
1691            as_ (str | Expression): the SQL code string to parse as the table expression.
1692                If an `Expression` instance is passed, it will be used as-is.
1693            recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`.
1694            append (bool): if `True`, add to any existing expressions.
1695                Otherwise, this resets the expressions.
1696            dialect (str): the dialect used to parse the input expression.
1697            copy (bool): if `False`, modify this expression instance in-place.
1698            opts (kwargs): other options to use to parse the input expressions.
1699
1700        Returns:
1701            Select: the modified expression.
1702        """
1703        alias_expression = maybe_parse(
1704            alias,
1705            dialect=dialect,
1706            into=TableAlias,
1707            **opts,
1708        )
1709        as_expression = maybe_parse(
1710            as_,
1711            dialect=dialect,
1712            **opts,
1713        )
1714        cte = CTE(
1715            this=as_expression,
1716            alias=alias_expression,
1717        )
1718        return _apply_child_list_builder(
1719            cte,
1720            instance=self,
1721            arg="with",
1722            append=append,
1723            copy=copy,
1724            into=With,
1725            properties={"recursive": recursive or False},
1726        )
1727
1728
1729QUERY_MODIFIERS = {
1730    "match": False,
1731    "laterals": False,
1732    "joins": False,
1733    "pivots": False,
1734    "where": False,
1735    "group": False,
1736    "having": False,
1737    "qualify": False,
1738    "windows": False,
1739    "distribute": False,
1740    "sort": False,
1741    "cluster": False,
1742    "order": False,
1743    "limit": False,
1744    "offset": False,
1745    "lock": False,
1746}
1747
1748
1749class Table(Expression):
1750    arg_types = {
1751        "this": True,
1752        "alias": False,
1753        "db": False,
1754        "catalog": False,
1755        "laterals": False,
1756        "joins": False,
1757        "pivots": False,
1758        "hints": False,
1759        "system_time": False,
1760    }
1761
1762    @property
1763    def db(self) -> str:
1764        return self.text("db")
1765
1766    @property
1767    def catalog(self) -> str:
1768        return self.text("catalog")
1769
1770
1771# See the TSQL "Querying data in a system-versioned temporal table" page
1772class SystemTime(Expression):
1773    arg_types = {
1774        "this": False,
1775        "expression": False,
1776        "kind": True,
1777    }
1778
1779
1780class Union(Subqueryable):
1781    arg_types = {
1782        "with": False,
1783        "this": True,
1784        "expression": True,
1785        "distinct": False,
1786        **QUERY_MODIFIERS,
1787    }
1788
1789    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1790        """
1791        Set the LIMIT expression.
1792
1793        Example:
1794            >>> select("1").union(select("1")).limit(1).sql()
1795            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1796
1797        Args:
1798            expression (str | int | Expression): the SQL code string to parse.
1799                This can also be an integer.
1800                If a `Limit` instance is passed, this is used as-is.
1801                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1802            dialect (str): the dialect used to parse the input expression.
1803            copy (bool): if `False`, modify this expression instance in-place.
1804            opts (kwargs): other options to use to parse the input expressions.
1805
1806        Returns:
1807            Select: The limited subqueryable.
1808        """
1809        return (
1810            select("*")
1811            .from_(self.subquery(alias="_l_0", copy=copy))
1812            .limit(expression, dialect=dialect, copy=False, **opts)
1813        )
1814
1815    def select(
1816        self,
1817        *expressions: str | Expression,
1818        append: bool = True,
1819        dialect: DialectType = None,
1820        copy: bool = True,
1821        **opts,
1822    ) -> Union:
1823        """Append to or set the SELECT of the union recursively.
1824
1825        Example:
1826            >>> from sqlglot import parse_one
1827            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
1828            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
1829
1830        Args:
1831            *expressions: the SQL code strings to parse.
1832                If an `Expression` instance is passed, it will be used as-is.
1833            append: if `True`, add to any existing expressions.
1834                Otherwise, this resets the expressions.
1835            dialect: the dialect used to parse the input expressions.
1836            copy: if `False`, modify this expression instance in-place.
1837            opts: other options to use to parse the input expressions.
1838
1839        Returns:
1840            Union: the modified expression.
1841        """
1842        this = self.copy() if copy else self
1843        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
1844        this.expression.unnest().select(
1845            *expressions, append=append, dialect=dialect, copy=False, **opts
1846        )
1847        return this
1848
1849    @property
1850    def named_selects(self):
1851        return self.this.unnest().named_selects
1852
1853    @property
1854    def is_star(self) -> bool:
1855        return self.this.is_star or self.expression.is_star
1856
1857    @property
1858    def selects(self):
1859        return self.this.unnest().selects
1860
1861    @property
1862    def left(self):
1863        return self.this
1864
1865    @property
1866    def right(self):
1867        return self.expression
1868
1869
1870class Except(Union):
1871    pass
1872
1873
1874class Intersect(Union):
1875    pass
1876
1877
1878class Unnest(UDTF):
1879    arg_types = {
1880        "expressions": True,
1881        "ordinality": False,
1882        "alias": False,
1883        "offset": False,
1884    }
1885
1886
1887class Update(Expression):
1888    arg_types = {
1889        "with": False,
1890        "this": False,
1891        "expressions": True,
1892        "from": False,
1893        "where": False,
1894    }
1895
1896
1897class Values(UDTF):
1898    arg_types = {
1899        "expressions": True,
1900        "ordinality": False,
1901        "alias": False,
1902    }
1903
1904
1905class Var(Expression):
1906    pass
1907
1908
1909class Schema(Expression):
1910    arg_types = {"this": False, "expressions": False}
1911
1912
1913# Used to represent the FOR UPDATE and FOR SHARE locking read types.
1914# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
1915class Lock(Expression):
1916    arg_types = {"update": True}
1917
1918
1919class Select(Subqueryable):
1920    arg_types = {
1921        "with": False,
1922        "expressions": False,
1923        "hint": False,
1924        "distinct": False,
1925        "into": False,
1926        "from": False,
1927        **QUERY_MODIFIERS,
1928    }
1929
1930    def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1931        """
1932        Set the FROM expression.
1933
1934        Example:
1935            >>> Select().from_("tbl").select("x").sql()
1936            'SELECT x FROM tbl'
1937
1938        Args:
1939            *expressions (str | Expression): the SQL code strings to parse.
1940                If a `From` instance is passed, this is used as-is.
1941                If another `Expression` instance is passed, it will be wrapped in a `From`.
1942            append (bool): if `True`, add to any existing expressions.
1943                Otherwise, this flattens all the `From` expression into a single expression.
1944            dialect (str): the dialect used to parse the input expression.
1945            copy (bool): if `False`, modify this expression instance in-place.
1946            opts (kwargs): other options to use to parse the input expressions.
1947
1948        Returns:
1949            Select: the modified expression.
1950        """
1951        return _apply_child_list_builder(
1952            *expressions,
1953            instance=self,
1954            arg="from",
1955            append=append,
1956            copy=copy,
1957            prefix="FROM",
1958            into=From,
1959            dialect=dialect,
1960            **opts,
1961        )
1962
1963    def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1964        """
1965        Set the GROUP BY expression.
1966
1967        Example:
1968            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
1969            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
1970
1971        Args:
1972            *expressions (str | Expression): the SQL code strings to parse.
1973                If a `Group` instance is passed, this is used as-is.
1974                If another `Expression` instance is passed, it will be wrapped in a `Group`.
1975                If nothing is passed in then a group by is not applied to the expression
1976            append (bool): if `True`, add to any existing expressions.
1977                Otherwise, this flattens all the `Group` expression into a single expression.
1978            dialect (str): the dialect used to parse the input expression.
1979            copy (bool): if `False`, modify this expression instance in-place.
1980            opts (kwargs): other options to use to parse the input expressions.
1981
1982        Returns:
1983            Select: the modified expression.
1984        """
1985        if not expressions:
1986            return self if not copy else self.copy()
1987        return _apply_child_list_builder(
1988            *expressions,
1989            instance=self,
1990            arg="group",
1991            append=append,
1992            copy=copy,
1993            prefix="GROUP BY",
1994            into=Group,
1995            dialect=dialect,
1996            **opts,
1997        )
1998
1999    def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2000        """
2001        Set the ORDER BY expression.
2002
2003        Example:
2004            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2005            'SELECT x FROM tbl ORDER BY x DESC'
2006
2007        Args:
2008            *expressions (str | Expression): the SQL code strings to parse.
2009                If a `Group` instance is passed, this is used as-is.
2010                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2011            append (bool): if `True`, add to any existing expressions.
2012                Otherwise, this flattens all the `Order` expression into a single expression.
2013            dialect (str): the dialect used to parse the input expression.
2014            copy (bool): if `False`, modify this expression instance in-place.
2015            opts (kwargs): other options to use to parse the input expressions.
2016
2017        Returns:
2018            Select: the modified expression.
2019        """
2020        return _apply_child_list_builder(
2021            *expressions,
2022            instance=self,
2023            arg="order",
2024            append=append,
2025            copy=copy,
2026            prefix="ORDER BY",
2027            into=Order,
2028            dialect=dialect,
2029            **opts,
2030        )
2031
2032    def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2033        """
2034        Set the SORT BY expression.
2035
2036        Example:
2037            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql()
2038            'SELECT x FROM tbl SORT BY x DESC'
2039
2040        Args:
2041            *expressions (str | Expression): the SQL code strings to parse.
2042                If a `Group` instance is passed, this is used as-is.
2043                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2044            append (bool): if `True`, add to any existing expressions.
2045                Otherwise, this flattens all the `Order` expression into a single expression.
2046            dialect (str): the dialect used to parse the input expression.
2047            copy (bool): if `False`, modify this expression instance in-place.
2048            opts (kwargs): other options to use to parse the input expressions.
2049
2050        Returns:
2051            Select: the modified expression.
2052        """
2053        return _apply_child_list_builder(
2054            *expressions,
2055            instance=self,
2056            arg="sort",
2057            append=append,
2058            copy=copy,
2059            prefix="SORT BY",
2060            into=Sort,
2061            dialect=dialect,
2062            **opts,
2063        )
2064
2065    def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2066        """
2067        Set the CLUSTER BY expression.
2068
2069        Example:
2070            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql()
2071            'SELECT x FROM tbl CLUSTER BY x DESC'
2072
2073        Args:
2074            *expressions (str | Expression): the SQL code strings to parse.
2075                If a `Group` instance is passed, this is used as-is.
2076                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2077            append (bool): if `True`, add to any existing expressions.
2078                Otherwise, this flattens all the `Order` expression into a single expression.
2079            dialect (str): the dialect used to parse the input expression.
2080            copy (bool): if `False`, modify this expression instance in-place.
2081            opts (kwargs): other options to use to parse the input expressions.
2082
2083        Returns:
2084            Select: the modified expression.
2085        """
2086        return _apply_child_list_builder(
2087            *expressions,
2088            instance=self,
2089            arg="cluster",
2090            append=append,
2091            copy=copy,
2092            prefix="CLUSTER BY",
2093            into=Cluster,
2094            dialect=dialect,
2095            **opts,
2096        )
2097
2098    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2099        """
2100        Set the LIMIT expression.
2101
2102        Example:
2103            >>> Select().from_("tbl").select("x").limit(10).sql()
2104            'SELECT x FROM tbl LIMIT 10'
2105
2106        Args:
2107            expression (str | int | Expression): the SQL code string to parse.
2108                This can also be an integer.
2109                If a `Limit` instance is passed, this is used as-is.
2110                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2111            dialect (str): the dialect used to parse the input expression.
2112            copy (bool): if `False`, modify this expression instance in-place.
2113            opts (kwargs): other options to use to parse the input expressions.
2114
2115        Returns:
2116            Select: the modified expression.
2117        """
2118        return _apply_builder(
2119            expression=expression,
2120            instance=self,
2121            arg="limit",
2122            into=Limit,
2123            prefix="LIMIT",
2124            dialect=dialect,
2125            copy=copy,
2126            **opts,
2127        )
2128
2129    def offset(self, expression, dialect=None, copy=True, **opts) -> Select:
2130        """
2131        Set the OFFSET expression.
2132
2133        Example:
2134            >>> Select().from_("tbl").select("x").offset(10).sql()
2135            'SELECT x FROM tbl OFFSET 10'
2136
2137        Args:
2138            expression (str | int | Expression): the SQL code string to parse.
2139                This can also be an integer.
2140                If a `Offset` instance is passed, this is used as-is.
2141                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2142            dialect (str): the dialect used to parse the input expression.
2143            copy (bool): if `False`, modify this expression instance in-place.
2144            opts (kwargs): other options to use to parse the input expressions.
2145
2146        Returns:
2147            Select: the modified expression.
2148        """
2149        return _apply_builder(
2150            expression=expression,
2151            instance=self,
2152            arg="offset",
2153            into=Offset,
2154            prefix="OFFSET",
2155            dialect=dialect,
2156            copy=copy,
2157            **opts,
2158        )
2159
2160    def select(
2161        self,
2162        *expressions: str | Expression,
2163        append: bool = True,
2164        dialect: DialectType = None,
2165        copy: bool = True,
2166        **opts,
2167    ) -> Select:
2168        """
2169        Append to or set the SELECT expressions.
2170
2171        Example:
2172            >>> Select().select("x", "y").sql()
2173            'SELECT x, y'
2174
2175        Args:
2176            *expressions: the SQL code strings to parse.
2177                If an `Expression` instance is passed, it will be used as-is.
2178            append: if `True`, add to any existing expressions.
2179                Otherwise, this resets the expressions.
2180            dialect: the dialect used to parse the input expressions.
2181            copy: if `False`, modify this expression instance in-place.
2182            opts: other options to use to parse the input expressions.
2183
2184        Returns:
2185            Select: the modified expression.
2186        """
2187        return _apply_list_builder(
2188            *expressions,
2189            instance=self,
2190            arg="expressions",
2191            append=append,
2192            dialect=dialect,
2193            copy=copy,
2194            **opts,
2195        )
2196
2197    def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2198        """
2199        Append to or set the LATERAL expressions.
2200
2201        Example:
2202            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2203            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2204
2205        Args:
2206            *expressions (str | Expression): the SQL code strings to parse.
2207                If an `Expression` instance is passed, it will be used as-is.
2208            append (bool): if `True`, add to any existing expressions.
2209                Otherwise, this resets the expressions.
2210            dialect (str): the dialect used to parse the input expressions.
2211            copy (bool): if `False`, modify this expression instance in-place.
2212            opts (kwargs): other options to use to parse the input expressions.
2213
2214        Returns:
2215            Select: the modified expression.
2216        """
2217        return _apply_list_builder(
2218            *expressions,
2219            instance=self,
2220            arg="laterals",
2221            append=append,
2222            into=Lateral,
2223            prefix="LATERAL VIEW",
2224            dialect=dialect,
2225            copy=copy,
2226            **opts,
2227        )
2228
2229    def join(
2230        self,
2231        expression,
2232        on=None,
2233        using=None,
2234        append=True,
2235        join_type=None,
2236        join_alias=None,
2237        dialect=None,
2238        copy=True,
2239        **opts,
2240    ) -> Select:
2241        """
2242        Append to or set the JOIN expressions.
2243
2244        Example:
2245            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2246            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2247
2248            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2249            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2250
2251            Use `join_type` to change the type of join:
2252
2253            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2254            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2255
2256        Args:
2257            expression (str | Expression): the SQL code string to parse.
2258                If an `Expression` instance is passed, it will be used as-is.
2259            on (str | Expression): optionally specify the join "on" criteria as a SQL string.
2260                If an `Expression` instance is passed, it will be used as-is.
2261            using (str | Expression): optionally specify the join "using" criteria as a SQL string.
2262                If an `Expression` instance is passed, it will be used as-is.
2263            append (bool): if `True`, add to any existing expressions.
2264                Otherwise, this resets the expressions.
2265            join_type (str): If set, alter the parsed join type
2266            dialect (str): the dialect used to parse the input expressions.
2267            copy (bool): if `False`, modify this expression instance in-place.
2268            opts (kwargs): other options to use to parse the input expressions.
2269
2270        Returns:
2271            Select: the modified expression.
2272        """
2273        parse_args = {"dialect": dialect, **opts}
2274
2275        try:
2276            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2277        except ParseError:
2278            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2279
2280        join = expression if isinstance(expression, Join) else Join(this=expression)
2281
2282        if isinstance(join.this, Select):
2283            join.this.replace(join.this.subquery())
2284
2285        if join_type:
2286            natural: t.Optional[Token]
2287            side: t.Optional[Token]
2288            kind: t.Optional[Token]
2289
2290            natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2291
2292            if natural:
2293                join.set("natural", True)
2294            if side:
2295                join.set("side", side.text)
2296            if kind:
2297                join.set("kind", kind.text)
2298
2299        if on:
2300            on = and_(*ensure_collection(on), dialect=dialect, **opts)
2301            join.set("on", on)
2302
2303        if using:
2304            join = _apply_list_builder(
2305                *ensure_collection(using),
2306                instance=join,
2307                arg="using",
2308                append=append,
2309                copy=copy,
2310                **opts,
2311            )
2312
2313        if join_alias:
2314            join.set("this", alias_(join.this, join_alias, table=True))
2315        return _apply_list_builder(
2316            join,
2317            instance=self,
2318            arg="joins",
2319            append=append,
2320            copy=copy,
2321            **opts,
2322        )
2323
2324    def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2325        """
2326        Append to or set the WHERE expressions.
2327
2328        Example:
2329            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
2330            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
2331
2332        Args:
2333            *expressions (str | Expression): the SQL code strings to parse.
2334                If an `Expression` instance is passed, it will be used as-is.
2335                Multiple expressions are combined with an AND operator.
2336            append (bool): if `True`, AND the new expressions to any existing expression.
2337                Otherwise, this resets the expression.
2338            dialect (str): the dialect used to parse the input expressions.
2339            copy (bool): if `False`, modify this expression instance in-place.
2340            opts (kwargs): other options to use to parse the input expressions.
2341
2342        Returns:
2343            Select: the modified expression.
2344        """
2345        return _apply_conjunction_builder(
2346            *expressions,
2347            instance=self,
2348            arg="where",
2349            append=append,
2350            into=Where,
2351            dialect=dialect,
2352            copy=copy,
2353            **opts,
2354        )
2355
2356    def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2357        """
2358        Append to or set the HAVING expressions.
2359
2360        Example:
2361            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
2362            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
2363
2364        Args:
2365            *expressions (str | Expression): the SQL code strings to parse.
2366                If an `Expression` instance is passed, it will be used as-is.
2367                Multiple expressions are combined with an AND operator.
2368            append (bool): if `True`, AND the new expressions to any existing expression.
2369                Otherwise, this resets the expression.
2370            dialect (str): the dialect used to parse the input expressions.
2371            copy (bool): if `False`, modify this expression instance in-place.
2372            opts (kwargs): other options to use to parse the input expressions.
2373
2374        Returns:
2375            Select: the modified expression.
2376        """
2377        return _apply_conjunction_builder(
2378            *expressions,
2379            instance=self,
2380            arg="having",
2381            append=append,
2382            into=Having,
2383            dialect=dialect,
2384            copy=copy,
2385            **opts,
2386        )
2387
2388    def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2389        return _apply_list_builder(
2390            *expressions,
2391            instance=self,
2392            arg="windows",
2393            append=append,
2394            into=Window,
2395            dialect=dialect,
2396            copy=copy,
2397            **opts,
2398        )
2399
2400    def distinct(self, distinct=True, copy=True) -> Select:
2401        """
2402        Set the OFFSET expression.
2403
2404        Example:
2405            >>> Select().from_("tbl").select("x").distinct().sql()
2406            'SELECT DISTINCT x FROM tbl'
2407
2408        Args:
2409            distinct (bool): whether the Select should be distinct
2410            copy (bool): if `False`, modify this expression instance in-place.
2411
2412        Returns:
2413            Select: the modified expression.
2414        """
2415        instance = _maybe_copy(self, copy)
2416        instance.set("distinct", Distinct() if distinct else None)
2417        return instance
2418
2419    def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create:
2420        """
2421        Convert this expression to a CREATE TABLE AS statement.
2422
2423        Example:
2424            >>> Select().select("*").from_("tbl").ctas("x").sql()
2425            'CREATE TABLE x AS SELECT * FROM tbl'
2426
2427        Args:
2428            table (str | Expression): the SQL code string to parse as the table name.
2429                If another `Expression` instance is passed, it will be used as-is.
2430            properties (dict): an optional mapping of table properties
2431            dialect (str): the dialect used to parse the input table.
2432            copy (bool): if `False`, modify this expression instance in-place.
2433            opts (kwargs): other options to use to parse the input table.
2434
2435        Returns:
2436            Create: the CREATE TABLE AS expression
2437        """
2438        instance = _maybe_copy(self, copy)
2439        table_expression = maybe_parse(
2440            table,
2441            into=Table,
2442            dialect=dialect,
2443            **opts,
2444        )
2445        properties_expression = None
2446        if properties:
2447            properties_expression = Properties.from_dict(properties)
2448
2449        return Create(
2450            this=table_expression,
2451            kind="table",
2452            expression=instance,
2453            properties=properties_expression,
2454        )
2455
2456    def lock(self, update: bool = True, copy: bool = True) -> Select:
2457        """
2458        Set the locking read mode for this expression.
2459
2460        Examples:
2461            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
2462            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
2463
2464            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
2465            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
2466
2467        Args:
2468            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
2469            copy: if `False`, modify this expression instance in-place.
2470
2471        Returns:
2472            The modified expression.
2473        """
2474
2475        inst = _maybe_copy(self, copy)
2476        inst.set("lock", Lock(update=update))
2477
2478        return inst
2479
2480    @property
2481    def named_selects(self) -> t.List[str]:
2482        return [e.output_name for e in self.expressions if e.alias_or_name]
2483
2484    @property
2485    def is_star(self) -> bool:
2486        return any(expression.is_star for expression in self.expressions)
2487
2488    @property
2489    def selects(self) -> t.List[Expression]:
2490        return self.expressions
2491
2492
2493class Subquery(DerivedTable, Unionable):
2494    arg_types = {
2495        "this": True,
2496        "alias": False,
2497        "with": False,
2498        **QUERY_MODIFIERS,
2499    }
2500
2501    def unnest(self):
2502        """
2503        Returns the first non subquery.
2504        """
2505        expression = self
2506        while isinstance(expression, Subquery):
2507            expression = expression.this
2508        return expression
2509
2510    @property
2511    def is_star(self) -> bool:
2512        return self.this.is_star
2513
2514    @property
2515    def output_name(self):
2516        return self.alias
2517
2518
2519class TableSample(Expression):
2520    arg_types = {
2521        "this": False,
2522        "method": False,
2523        "bucket_numerator": False,
2524        "bucket_denominator": False,
2525        "bucket_field": False,
2526        "percent": False,
2527        "rows": False,
2528        "size": False,
2529        "seed": False,
2530    }
2531
2532
2533class Tag(Expression):
2534    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
2535
2536    arg_types = {
2537        "this": False,
2538        "prefix": False,
2539        "postfix": False,
2540    }
2541
2542
2543class Pivot(Expression):
2544    arg_types = {
2545        "this": False,
2546        "alias": False,
2547        "expressions": True,
2548        "field": True,
2549        "unpivot": True,
2550    }
2551
2552
2553class Window(Expression):
2554    arg_types = {
2555        "this": True,
2556        "partition_by": False,
2557        "order": False,
2558        "spec": False,
2559        "alias": False,
2560    }
2561
2562
2563class WindowSpec(Expression):
2564    arg_types = {
2565        "kind": False,
2566        "start": False,
2567        "start_side": False,
2568        "end": False,
2569        "end_side": False,
2570    }
2571
2572
2573class Where(Expression):
2574    pass
2575
2576
2577class Star(Expression):
2578    arg_types = {"except": False, "replace": False}
2579
2580    @property
2581    def name(self) -> str:
2582        return "*"
2583
2584    @property
2585    def output_name(self):
2586        return self.name
2587
2588
2589class Parameter(Expression):
2590    arg_types = {"this": True, "wrapped": False}
2591
2592
2593class SessionParameter(Expression):
2594    arg_types = {"this": True, "kind": False}
2595
2596
2597class Placeholder(Expression):
2598    arg_types = {"this": False}
2599
2600
2601class Null(Condition):
2602    arg_types: t.Dict[str, t.Any] = {}
2603
2604    @property
2605    def name(self) -> str:
2606        return "NULL"
2607
2608
2609class Boolean(Condition):
2610    pass
2611
2612
2613class DataType(Expression):
2614    arg_types = {
2615        "this": True,
2616        "expressions": False,
2617        "nested": False,
2618        "values": False,
2619        "prefix": False,
2620    }
2621
2622    class Type(AutoName):
2623        CHAR = auto()
2624        NCHAR = auto()
2625        VARCHAR = auto()
2626        NVARCHAR = auto()
2627        TEXT = auto()
2628        MEDIUMTEXT = auto()
2629        LONGTEXT = auto()
2630        MEDIUMBLOB = auto()
2631        LONGBLOB = auto()
2632        BINARY = auto()
2633        VARBINARY = auto()
2634        INT = auto()
2635        TINYINT = auto()
2636        SMALLINT = auto()
2637        BIGINT = auto()
2638        FLOAT = auto()
2639        DOUBLE = auto()
2640        DECIMAL = auto()
2641        BOOLEAN = auto()
2642        JSON = auto()
2643        JSONB = auto()
2644        INTERVAL = auto()
2645        TIME = auto()
2646        TIMESTAMP = auto()
2647        TIMESTAMPTZ = auto()
2648        TIMESTAMPLTZ = auto()
2649        DATE = auto()
2650        DATETIME = auto()
2651        ARRAY = auto()
2652        MAP = auto()
2653        UUID = auto()
2654        GEOGRAPHY = auto()
2655        GEOMETRY = auto()
2656        STRUCT = auto()
2657        NULLABLE = auto()
2658        HLLSKETCH = auto()
2659        HSTORE = auto()
2660        SUPER = auto()
2661        SERIAL = auto()
2662        SMALLSERIAL = auto()
2663        BIGSERIAL = auto()
2664        XML = auto()
2665        UNIQUEIDENTIFIER = auto()
2666        MONEY = auto()
2667        SMALLMONEY = auto()
2668        ROWVERSION = auto()
2669        IMAGE = auto()
2670        VARIANT = auto()
2671        OBJECT = auto()
2672        INET = auto()
2673        NULL = auto()
2674        UNKNOWN = auto()  # Sentinel value, useful for type annotation
2675
2676    TEXT_TYPES = {
2677        Type.CHAR,
2678        Type.NCHAR,
2679        Type.VARCHAR,
2680        Type.NVARCHAR,
2681        Type.TEXT,
2682    }
2683
2684    INTEGER_TYPES = {
2685        Type.INT,
2686        Type.TINYINT,
2687        Type.SMALLINT,
2688        Type.BIGINT,
2689    }
2690
2691    FLOAT_TYPES = {
2692        Type.FLOAT,
2693        Type.DOUBLE,
2694    }
2695
2696    NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES}
2697
2698    TEMPORAL_TYPES = {
2699        Type.TIMESTAMP,
2700        Type.TIMESTAMPTZ,
2701        Type.TIMESTAMPLTZ,
2702        Type.DATE,
2703        Type.DATETIME,
2704    }
2705
2706    @classmethod
2707    def build(
2708        cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs
2709    ) -> DataType:
2710        from sqlglot import parse_one
2711
2712        if isinstance(dtype, str):
2713            if dtype.upper() in cls.Type.__members__:
2714                data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()])
2715            else:
2716                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
2717            if data_type_exp is None:
2718                raise ValueError(f"Unparsable data type value: {dtype}")
2719        elif isinstance(dtype, DataType.Type):
2720            data_type_exp = DataType(this=dtype)
2721        elif isinstance(dtype, DataType):
2722            return dtype
2723        else:
2724            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
2725        return DataType(**{**data_type_exp.args, **kwargs})
2726
2727    def is_type(self, dtype: DataType.Type) -> bool:
2728        return self.this == dtype
2729
2730
2731# https://www.postgresql.org/docs/15/datatype-pseudo.html
2732class PseudoType(Expression):
2733    pass
2734
2735
2736class StructKwarg(Expression):
2737    arg_types = {"this": True, "expression": True}
2738
2739
2740# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
2741class SubqueryPredicate(Predicate):
2742    pass
2743
2744
2745class All(SubqueryPredicate):
2746    pass
2747
2748
2749class Any(SubqueryPredicate):
2750    pass
2751
2752
2753class Exists(SubqueryPredicate):
2754    pass
2755
2756
2757# Commands to interact with the databases or engines. For most of the command
2758# expressions we parse whatever comes after the command's name as a string.
2759class Command(Expression):
2760    arg_types = {"this": True, "expression": False}
2761
2762
2763class Transaction(Expression):
2764    arg_types = {"this": False, "modes": False}
2765
2766
2767class Commit(Expression):
2768    arg_types = {"chain": False}
2769
2770
2771class Rollback(Expression):
2772    arg_types = {"savepoint": False}
2773
2774
2775class AlterTable(Expression):
2776    arg_types = {"this": True, "actions": True, "exists": False}
2777
2778
2779class AddConstraint(Expression):
2780    arg_types = {"this": False, "expression": False, "enforced": False}
2781
2782
2783class DropPartition(Expression):
2784    arg_types = {"expressions": True, "exists": False}
2785
2786
2787# Binary expressions like (ADD a b)
2788class Binary(Expression):
2789    arg_types = {"this": True, "expression": True}
2790
2791    @property
2792    def left(self):
2793        return self.this
2794
2795    @property
2796    def right(self):
2797        return self.expression
2798
2799
2800class Add(Binary):
2801    pass
2802
2803
2804class Connector(Binary, Condition):
2805    pass
2806
2807
2808class And(Connector):
2809    pass
2810
2811
2812class Or(Connector):
2813    pass
2814
2815
2816class BitwiseAnd(Binary):
2817    pass
2818
2819
2820class BitwiseLeftShift(Binary):
2821    pass
2822
2823
2824class BitwiseOr(Binary):
2825    pass
2826
2827
2828class BitwiseRightShift(Binary):
2829    pass
2830
2831
2832class BitwiseXor(Binary):
2833    pass
2834
2835
2836class Div(Binary):
2837    pass
2838
2839
2840class Overlaps(Binary):
2841    pass
2842
2843
2844class Dot(Binary):
2845    @property
2846    def name(self) -> str:
2847        return self.expression.name
2848
2849
2850class DPipe(Binary):
2851    pass
2852
2853
2854class EQ(Binary, Predicate):
2855    pass
2856
2857
2858class NullSafeEQ(Binary, Predicate):
2859    pass
2860
2861
2862class NullSafeNEQ(Binary, Predicate):
2863    pass
2864
2865
2866class Distance(Binary):
2867    pass
2868
2869
2870class Escape(Binary):
2871    pass
2872
2873
2874class Glob(Binary, Predicate):
2875    pass
2876
2877
2878class GT(Binary, Predicate):
2879    pass
2880
2881
2882class GTE(Binary, Predicate):
2883    pass
2884
2885
2886class ILike(Binary, Predicate):
2887    pass
2888
2889
2890class ILikeAny(Binary, Predicate):
2891    pass
2892
2893
2894class IntDiv(Binary):
2895    pass
2896
2897
2898class Is(Binary, Predicate):
2899    pass
2900
2901
2902class Kwarg(Binary):
2903    """Kwarg in special functions like func(kwarg => y)."""
2904
2905
2906class Like(Binary, Predicate):
2907    pass
2908
2909
2910class LikeAny(Binary, Predicate):
2911    pass
2912
2913
2914class LT(Binary, Predicate):
2915    pass
2916
2917
2918class LTE(Binary, Predicate):
2919    pass
2920
2921
2922class Mod(Binary):
2923    pass
2924
2925
2926class Mul(Binary):
2927    pass
2928
2929
2930class NEQ(Binary, Predicate):
2931    pass
2932
2933
2934class SimilarTo(Binary, Predicate):
2935    pass
2936
2937
2938class Slice(Binary):
2939    arg_types = {"this": False, "expression": False}
2940
2941
2942class Sub(Binary):
2943    pass
2944
2945
2946# Unary Expressions
2947# (NOT a)
2948class Unary(Expression):
2949    pass
2950
2951
2952class BitwiseNot(Unary):
2953    pass
2954
2955
2956class Not(Unary, Condition):
2957    pass
2958
2959
2960class Paren(Unary, Condition):
2961    arg_types = {"this": True, "with": False}
2962
2963
2964class Neg(Unary):
2965    pass
2966
2967
2968# Special Functions
2969class Alias(Expression):
2970    arg_types = {"this": True, "alias": False}
2971
2972    @property
2973    def output_name(self):
2974        return self.alias
2975
2976
2977class Aliases(Expression):
2978    arg_types = {"this": True, "expressions": True}
2979
2980    @property
2981    def aliases(self):
2982        return self.expressions
2983
2984
2985class AtTimeZone(Expression):
2986    arg_types = {"this": True, "zone": True}
2987
2988
2989class Between(Predicate):
2990    arg_types = {"this": True, "low": True, "high": True}
2991
2992
2993class Bracket(Condition):
2994    arg_types = {"this": True, "expressions": True}
2995
2996
2997class Distinct(Expression):
2998    arg_types = {"expressions": False, "on": False}
2999
3000
3001class In(Predicate):
3002    arg_types = {
3003        "this": True,
3004        "expressions": False,
3005        "query": False,
3006        "unnest": False,
3007        "field": False,
3008        "is_global": False,
3009    }
3010
3011
3012class TimeUnit(Expression):
3013    """Automatically converts unit arg into a var."""
3014
3015    arg_types = {"unit": False}
3016
3017    def __init__(self, **args):
3018        unit = args.get("unit")
3019        if isinstance(unit, Column):
3020            args["unit"] = Var(this=unit.name)
3021        elif isinstance(unit, Week):
3022            unit.set("this", Var(this=unit.this.name))
3023        super().__init__(**args)
3024
3025
3026class Interval(TimeUnit):
3027    arg_types = {"this": False, "unit": False}
3028
3029
3030class IgnoreNulls(Expression):
3031    pass
3032
3033
3034class RespectNulls(Expression):
3035    pass
3036
3037
3038# Functions
3039class Func(Condition):
3040    """
3041    The base class for all function expressions.
3042
3043    Attributes:
3044        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
3045            treated as a variable length argument and the argument's value will be stored as a list.
3046        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
3047            for this function expression. These values are used to map this node to a name during parsing
3048            as well as to provide the function's name during SQL string generation. By default the SQL
3049            name is set to the expression's class name transformed to snake case.
3050    """
3051
3052    is_var_len_args = False
3053
3054    @classmethod
3055    def from_arg_list(cls, args):
3056        if cls.is_var_len_args:
3057            all_arg_keys = list(cls.arg_types)
3058            # If this function supports variable length argument treat the last argument as such.
3059            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
3060            num_non_var = len(non_var_len_arg_keys)
3061
3062            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
3063            args_dict[all_arg_keys[-1]] = args[num_non_var:]
3064        else:
3065            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
3066
3067        return cls(**args_dict)
3068
3069    @classmethod
3070    def sql_names(cls):
3071        if cls is Func:
3072            raise NotImplementedError(
3073                "SQL name is only supported by concrete function implementations"
3074            )
3075        if "_sql_names" not in cls.__dict__:
3076            cls._sql_names = [camel_to_snake_case(cls.__name__)]
3077        return cls._sql_names
3078
3079    @classmethod
3080    def sql_name(cls):
3081        return cls.sql_names()[0]
3082
3083    @classmethod
3084    def default_parser_mappings(cls):
3085        return {name: cls.from_arg_list for name in cls.sql_names()}
3086
3087
3088class AggFunc(Func):
3089    pass
3090
3091
3092class Abs(Func):
3093    pass
3094
3095
3096class Anonymous(Func):
3097    arg_types = {"this": True, "expressions": False}
3098    is_var_len_args = True
3099
3100
3101class ApproxDistinct(AggFunc):
3102    arg_types = {"this": True, "accuracy": False}
3103
3104
3105class Array(Func):
3106    arg_types = {"expressions": False}
3107    is_var_len_args = True
3108
3109
3110class GenerateSeries(Func):
3111    arg_types = {"start": True, "end": True, "step": False}
3112
3113
3114class ArrayAgg(AggFunc):
3115    pass
3116
3117
3118class ArrayAll(Func):
3119    arg_types = {"this": True, "expression": True}
3120
3121
3122class ArrayAny(Func):
3123    arg_types = {"this": True, "expression": True}
3124
3125
3126class ArrayConcat(Func):
3127    arg_types = {"this": True, "expressions": False}
3128    is_var_len_args = True
3129
3130
3131class ArrayContains(Func):
3132    arg_types = {"this": True, "expression": True}
3133
3134
3135class ArrayFilter(Func):
3136    arg_types = {"this": True, "expression": True}
3137    _sql_names = ["FILTER", "ARRAY_FILTER"]
3138
3139
3140class ArrayJoin(Func):
3141    arg_types = {"this": True, "expression": True, "null": False}
3142
3143
3144class ArraySize(Func):
3145    arg_types = {"this": True, "expression": False}
3146
3147
3148class ArraySort(Func):
3149    arg_types = {"this": True, "expression": False}
3150
3151
3152class ArraySum(Func):
3153    pass
3154
3155
3156class ArrayUnionAgg(AggFunc):
3157    pass
3158
3159
3160class Avg(AggFunc):
3161    pass
3162
3163
3164class AnyValue(AggFunc):
3165    pass
3166
3167
3168class Case(Func):
3169    arg_types = {"this": False, "ifs": True, "default": False}
3170
3171
3172class Cast(Func):
3173    arg_types = {"this": True, "to": True}
3174
3175    @property
3176    def name(self) -> str:
3177        return self.this.name
3178
3179    @property
3180    def to(self):
3181        return self.args["to"]
3182
3183    @property
3184    def output_name(self):
3185        return self.name
3186
3187    def is_type(self, dtype: DataType.Type) -> bool:
3188        return self.to.is_type(dtype)
3189
3190
3191class Collate(Binary):
3192    pass
3193
3194
3195class TryCast(Cast):
3196    pass
3197
3198
3199class Ceil(Func):
3200    arg_types = {"this": True, "decimals": False}
3201    _sql_names = ["CEIL", "CEILING"]
3202
3203
3204class Coalesce(Func):
3205    arg_types = {"this": True, "expressions": False}
3206    is_var_len_args = True
3207
3208
3209class Concat(Func):
3210    arg_types = {"expressions": True}
3211    is_var_len_args = True
3212
3213
3214class ConcatWs(Concat):
3215    _sql_names = ["CONCAT_WS"]
3216
3217
3218class Count(AggFunc):
3219    arg_types = {"this": False}
3220
3221
3222class CurrentDate(Func):
3223    arg_types = {"this": False}
3224
3225
3226class CurrentDatetime(Func):
3227    arg_types = {"this": False}
3228
3229
3230class CurrentTime(Func):
3231    arg_types = {"this": False}
3232
3233
3234class CurrentTimestamp(Func):
3235    arg_types = {"this": False}
3236
3237
3238class DateAdd(Func, TimeUnit):
3239    arg_types = {"this": True, "expression": True, "unit": False}
3240
3241
3242class DateSub(Func, TimeUnit):
3243    arg_types = {"this": True, "expression": True, "unit": False}
3244
3245
3246class DateDiff(Func, TimeUnit):
3247    arg_types = {"this": True, "expression": True, "unit": False}
3248
3249
3250class DateTrunc(Func):
3251    arg_types = {"unit": True, "this": True, "zone": False}
3252
3253
3254class DatetimeAdd(Func, TimeUnit):
3255    arg_types = {"this": True, "expression": True, "unit": False}
3256
3257
3258class DatetimeSub(Func, TimeUnit):
3259    arg_types = {"this": True, "expression": True, "unit": False}
3260
3261
3262class DatetimeDiff(Func, TimeUnit):
3263    arg_types = {"this": True, "expression": True, "unit": False}
3264
3265
3266class DatetimeTrunc(Func, TimeUnit):
3267    arg_types = {"this": True, "unit": True, "zone": False}
3268
3269
3270class DayOfWeek(Func):
3271    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
3272
3273
3274class DayOfMonth(Func):
3275    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
3276
3277
3278class DayOfYear(Func):
3279    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
3280
3281
3282class WeekOfYear(Func):
3283    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
3284
3285
3286class LastDateOfMonth(Func):
3287    pass
3288
3289
3290class Extract(Func):
3291    arg_types = {"this": True, "expression": True}
3292
3293
3294class TimestampAdd(Func, TimeUnit):
3295    arg_types = {"this": True, "expression": True, "unit": False}
3296
3297
3298class TimestampSub(Func, TimeUnit):
3299    arg_types = {"this": True, "expression": True, "unit": False}
3300
3301
3302class TimestampDiff(Func, TimeUnit):
3303    arg_types = {"this": True, "expression": True, "unit": False}
3304
3305
3306class TimestampTrunc(Func, TimeUnit):
3307    arg_types = {"this": True, "unit": True, "zone": False}
3308
3309
3310class TimeAdd(Func, TimeUnit):
3311    arg_types = {"this": True, "expression": True, "unit": False}
3312
3313
3314class TimeSub(Func, TimeUnit):
3315    arg_types = {"this": True, "expression": True, "unit": False}
3316
3317
3318class TimeDiff(Func, TimeUnit):
3319    arg_types = {"this": True, "expression": True, "unit": False}
3320
3321
3322class TimeTrunc(Func, TimeUnit):
3323    arg_types = {"this": True, "unit": True, "zone": False}
3324
3325
3326class DateFromParts(Func):
3327    _sql_names = ["DATEFROMPARTS"]
3328    arg_types = {"year": True, "month": True, "day": True}
3329
3330
3331class DateStrToDate(Func):
3332    pass
3333
3334
3335class DateToDateStr(Func):
3336    pass
3337
3338
3339class DateToDi(Func):
3340    pass
3341
3342
3343class Day(Func):
3344    pass
3345
3346
3347class Decode(Func):
3348    arg_types = {"this": True, "charset": True, "replace": False}
3349
3350
3351class DiToDate(Func):
3352    pass
3353
3354
3355class Encode(Func):
3356    arg_types = {"this": True, "charset": True}
3357
3358
3359class Exp(Func):
3360    pass
3361
3362
3363class Explode(Func):
3364    pass
3365
3366
3367class Floor(Func):
3368    arg_types = {"this": True, "decimals": False}
3369
3370
3371class Greatest(Func):
3372    arg_types = {"this": True, "expressions": False}
3373    is_var_len_args = True
3374
3375
3376class GroupConcat(Func):
3377    arg_types = {"this": True, "separator": False}
3378
3379
3380class Hex(Func):
3381    pass
3382
3383
3384class If(Func):
3385    arg_types = {"this": True, "true": True, "false": False}
3386
3387
3388class IfNull(Func):
3389    arg_types = {"this": True, "expression": False}
3390    _sql_names = ["IFNULL", "NVL"]
3391
3392
3393class Initcap(Func):
3394    pass
3395
3396
3397class JSONBContains(Binary):
3398    _sql_names = ["JSONB_CONTAINS"]
3399
3400
3401class JSONExtract(Binary, Func):
3402    _sql_names = ["JSON_EXTRACT"]
3403
3404
3405class JSONExtractScalar(JSONExtract):
3406    _sql_names = ["JSON_EXTRACT_SCALAR"]
3407
3408
3409class JSONBExtract(JSONExtract):
3410    _sql_names = ["JSONB_EXTRACT"]
3411
3412
3413class JSONBExtractScalar(JSONExtract):
3414    _sql_names = ["JSONB_EXTRACT_SCALAR"]
3415
3416
3417class Least(Func):
3418    arg_types = {"this": True, "expressions": False}
3419    is_var_len_args = True
3420
3421
3422class Length(Func):
3423    pass
3424
3425
3426class Levenshtein(Func):
3427    arg_types = {
3428        "this": True,
3429        "expression": False,
3430        "ins_cost": False,
3431        "del_cost": False,
3432        "sub_cost": False,
3433    }
3434
3435
3436class Ln(Func):
3437    pass
3438
3439
3440class Log(Func):
3441    arg_types = {"this": True, "expression": False}
3442
3443
3444class Log2(Func):
3445    pass
3446
3447
3448class Log10(Func):
3449    pass
3450
3451
3452class LogicalOr(AggFunc):
3453    _sql_names = ["LOGICAL_OR", "BOOL_OR"]
3454
3455
3456class Lower(Func):
3457    _sql_names = ["LOWER", "LCASE"]
3458
3459
3460class Map(Func):
3461    arg_types = {"keys": False, "values": False}
3462
3463
3464class VarMap(Func):
3465    arg_types = {"keys": True, "values": True}
3466    is_var_len_args = True
3467
3468
3469class Matches(Func):
3470    """Oracle/Snowflake decode.
3471    https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm
3472    Pattern matching MATCHES(value, search1, result1, ...searchN, resultN, else)
3473    """
3474
3475    arg_types = {"this": True, "expressions": True}
3476    is_var_len_args = True
3477
3478
3479class Max(AggFunc):
3480    arg_types = {"this": True, "expression": False}
3481
3482
3483class Min(AggFunc):
3484    arg_types = {"this": True, "expression": False}
3485
3486
3487class Month(Func):
3488    pass
3489
3490
3491class Nvl2(Func):
3492    arg_types = {"this": True, "true": True, "false": False}
3493
3494
3495class Posexplode(Func):
3496    pass
3497
3498
3499class Pow(Binary, Func):
3500    _sql_names = ["POWER", "POW"]
3501
3502
3503class PercentileCont(AggFunc):
3504    pass
3505
3506
3507class PercentileDisc(AggFunc):
3508    pass
3509
3510
3511class Quantile(AggFunc):
3512    arg_types = {"this": True, "quantile": True}
3513
3514
3515# Clickhouse-specific:
3516# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles
3517class Quantiles(AggFunc):
3518    arg_types = {"parameters": True, "expressions": True}
3519
3520
3521class QuantileIf(AggFunc):
3522    arg_types = {"parameters": True, "expressions": True}
3523
3524
3525class ApproxQuantile(Quantile):
3526    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
3527
3528
3529class RangeN(Func):
3530    arg_types = {"this": True, "expressions": True, "each": False}
3531
3532
3533class ReadCSV(Func):
3534    _sql_names = ["READ_CSV"]
3535    is_var_len_args = True
3536    arg_types = {"this": True, "expressions": False}
3537
3538
3539class Reduce(Func):
3540    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
3541
3542
3543class RegexpExtract(Func):
3544    arg_types = {
3545        "this": True,
3546        "expression": True,
3547        "position": False,
3548        "occurrence": False,
3549        "group": False,
3550    }
3551
3552
3553class RegexpLike(Func):
3554    arg_types = {"this": True, "expression": True, "flag": False}
3555
3556
3557class RegexpILike(Func):
3558    arg_types = {"this": True, "expression": True, "flag": False}
3559
3560
3561class RegexpSplit(Func):
3562    arg_types = {"this": True, "expression": True}
3563
3564
3565class Repeat(Func):
3566    arg_types = {"this": True, "times": True}
3567
3568
3569class Round(Func):
3570    arg_types = {"this": True, "decimals": False}
3571
3572
3573class RowNumber(Func):
3574    arg_types: t.Dict[str, t.Any] = {}
3575
3576
3577class SafeDivide(Func):
3578    arg_types = {"this": True, "expression": True}
3579
3580
3581class SetAgg(AggFunc):
3582    pass
3583
3584
3585class SortArray(Func):
3586    arg_types = {"this": True, "asc": False}
3587
3588
3589class Split(Func):
3590    arg_types = {"this": True, "expression": True, "limit": False}
3591
3592
3593# Start may be omitted in the case of postgres
3594# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
3595class Substring(Func):
3596    arg_types = {"this": True, "start": False, "length": False}
3597
3598
3599class StrPosition(Func):
3600    arg_types = {
3601        "this": True,
3602        "substr": True,
3603        "position": False,
3604        "instance": False,
3605    }
3606
3607
3608class StrToDate(Func):
3609    arg_types = {"this": True, "format": True}
3610
3611
3612class StrToTime(Func):
3613    arg_types = {"this": True, "format": True}
3614
3615
3616# Spark allows unix_timestamp()
3617# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
3618class StrToUnix(Func):
3619    arg_types = {"this": False, "format": False}
3620
3621
3622class NumberToStr(Func):
3623    arg_types = {"this": True, "format": True}
3624
3625
3626class Struct(Func):
3627    arg_types = {"expressions": True}
3628    is_var_len_args = True
3629
3630
3631class StructExtract(Func):
3632    arg_types = {"this": True, "expression": True}
3633
3634
3635class Sum(AggFunc):
3636    pass
3637
3638
3639class Sqrt(Func):
3640    pass
3641
3642
3643class Stddev(AggFunc):
3644    pass
3645
3646
3647class StddevPop(AggFunc):
3648    pass
3649
3650
3651class StddevSamp(AggFunc):
3652    pass
3653
3654
3655class TimeToStr(Func):
3656    arg_types = {"this": True, "format": True}
3657
3658
3659class TimeToTimeStr(Func):
3660    pass
3661
3662
3663class TimeToUnix(Func):
3664    pass
3665
3666
3667class TimeStrToDate(Func):
3668    pass
3669
3670
3671class TimeStrToTime(Func):
3672    pass
3673
3674
3675class TimeStrToUnix(Func):
3676    pass
3677
3678
3679class Trim(Func):
3680    arg_types = {
3681        "this": True,
3682        "expression": False,
3683        "position": False,
3684        "collation": False,
3685    }
3686
3687
3688class TsOrDsAdd(Func, TimeUnit):
3689    arg_types = {"this": True, "expression": True, "unit": False}
3690
3691
3692class TsOrDsToDateStr(Func):
3693    pass
3694
3695
3696class TsOrDsToDate(Func):
3697    arg_types = {"this": True, "format": False}
3698
3699
3700class TsOrDiToDi(Func):
3701    pass
3702
3703
3704class Unhex(Func):
3705    pass
3706
3707
3708class UnixToStr(Func):
3709    arg_types = {"this": True, "format": False}
3710
3711
3712# https://prestodb.io/docs/current/functions/datetime.html
3713# presto has weird zone/hours/minutes
3714class UnixToTime(Func):
3715    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
3716
3717    SECONDS = Literal.string("seconds")
3718    MILLIS = Literal.string("millis")
3719    MICROS = Literal.string("micros")
3720
3721
3722class UnixToTimeStr(Func):
3723    pass
3724
3725
3726class Upper(Func):
3727    _sql_names = ["UPPER", "UCASE"]
3728
3729
3730class Variance(AggFunc):
3731    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
3732
3733
3734class VariancePop(AggFunc):
3735    _sql_names = ["VARIANCE_POP", "VAR_POP"]
3736
3737
3738class Week(Func):
3739    arg_types = {"this": True, "mode": False}
3740
3741
3742class XMLTable(Func):
3743    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
3744
3745
3746class Year(Func):
3747    pass
3748
3749
3750class Use(Expression):
3751    arg_types = {"this": True, "kind": False}
3752
3753
3754class Merge(Expression):
3755    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
3756
3757
3758class When(Func):
3759    arg_types = {"this": True, "then": True}
3760
3761
3762def _norm_args(expression):
3763    args = {}
3764
3765    for k, arg in expression.args.items():
3766        if isinstance(arg, list):
3767            arg = [_norm_arg(a) for a in arg]
3768            if not arg:
3769                arg = None
3770        else:
3771            arg = _norm_arg(arg)
3772
3773        if arg is not None and arg is not False:
3774            args[k] = arg
3775
3776    return args
3777
3778
3779def _norm_arg(arg):
3780    return arg.lower() if isinstance(arg, str) else arg
3781
3782
3783ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
3784
3785
3786# Helpers
3787def maybe_parse(
3788    sql_or_expression: str | Expression,
3789    *,
3790    into: t.Optional[IntoType] = None,
3791    dialect: DialectType = None,
3792    prefix: t.Optional[str] = None,
3793    copy: bool = False,
3794    **opts,
3795) -> Expression:
3796    """Gracefully handle a possible string or expression.
3797
3798    Example:
3799        >>> maybe_parse("1")
3800        (LITERAL this: 1, is_string: False)
3801        >>> maybe_parse(to_identifier("x"))
3802        (IDENTIFIER this: x, quoted: False)
3803
3804    Args:
3805        sql_or_expression: the SQL code string or an expression
3806        into: the SQLGlot Expression to parse into
3807        dialect: the dialect used to parse the input expressions (in the case that an
3808            input expression is a SQL string).
3809        prefix: a string to prefix the sql with before it gets parsed
3810            (automatically includes a space)
3811        copy: whether or not to copy the expression.
3812        **opts: other options to use to parse the input expressions (again, in the case
3813            that an input expression is a SQL string).
3814
3815    Returns:
3816        Expression: the parsed or given expression.
3817    """
3818    if isinstance(sql_or_expression, Expression):
3819        if copy:
3820            return sql_or_expression.copy()
3821        return sql_or_expression
3822
3823    import sqlglot
3824
3825    sql = str(sql_or_expression)
3826    if prefix:
3827        sql = f"{prefix} {sql}"
3828    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
3829
3830
3831def _maybe_copy(instance, copy=True):
3832    return instance.copy() if copy else instance
3833
3834
3835def _is_wrong_expression(expression, into):
3836    return isinstance(expression, Expression) and not isinstance(expression, into)
3837
3838
3839def _apply_builder(
3840    expression,
3841    instance,
3842    arg,
3843    copy=True,
3844    prefix=None,
3845    into=None,
3846    dialect=None,
3847    **opts,
3848):
3849    if _is_wrong_expression(expression, into):
3850        expression = into(this=expression)
3851    instance = _maybe_copy(instance, copy)
3852    expression = maybe_parse(
3853        sql_or_expression=expression,
3854        prefix=prefix,
3855        into=into,
3856        dialect=dialect,
3857        **opts,
3858    )
3859    instance.set(arg, expression)
3860    return instance
3861
3862
3863def _apply_child_list_builder(
3864    *expressions,
3865    instance,
3866    arg,
3867    append=True,
3868    copy=True,
3869    prefix=None,
3870    into=None,
3871    dialect=None,
3872    properties=None,
3873    **opts,
3874):
3875    instance = _maybe_copy(instance, copy)
3876    parsed = []
3877    for expression in expressions:
3878        if _is_wrong_expression(expression, into):
3879            expression = into(expressions=[expression])
3880        expression = maybe_parse(
3881            expression,
3882            into=into,
3883            dialect=dialect,
3884            prefix=prefix,
3885            **opts,
3886        )
3887        parsed.extend(expression.expressions)
3888
3889    existing = instance.args.get(arg)
3890    if append and existing:
3891        parsed = existing.expressions + parsed
3892
3893    child = into(expressions=parsed)
3894    for k, v in (properties or {}).items():
3895        child.set(k, v)
3896    instance.set(arg, child)
3897    return instance
3898
3899
3900def _apply_list_builder(
3901    *expressions,
3902    instance,
3903    arg,
3904    append=True,
3905    copy=True,
3906    prefix=None,
3907    into=None,
3908    dialect=None,
3909    **opts,
3910):
3911    inst = _maybe_copy(instance, copy)
3912
3913    expressions = [
3914        maybe_parse(
3915            sql_or_expression=expression,
3916            into=into,
3917            prefix=prefix,
3918            dialect=dialect,
3919            **opts,
3920        )
3921        for expression in expressions
3922    ]
3923
3924    existing_expressions = inst.args.get(arg)
3925    if append and existing_expressions:
3926        expressions = existing_expressions + expressions
3927
3928    inst.set(arg, expressions)
3929    return inst
3930
3931
3932def _apply_conjunction_builder(
3933    *expressions,
3934    instance,
3935    arg,
3936    into=None,
3937    append=True,
3938    copy=True,
3939    dialect=None,
3940    **opts,
3941):
3942    expressions = [exp for exp in expressions if exp is not None and exp != ""]
3943    if not expressions:
3944        return instance
3945
3946    inst = _maybe_copy(instance, copy)
3947
3948    existing = inst.args.get(arg)
3949    if append and existing is not None:
3950        expressions = [existing.this if into else existing] + list(expressions)
3951
3952    node = and_(*expressions, dialect=dialect, **opts)
3953
3954    inst.set(arg, into(this=node) if into else node)
3955    return inst
3956
3957
3958def _combine(expressions, operator, dialect=None, **opts):
3959    expressions = [condition(expression, dialect=dialect, **opts) for expression in expressions]
3960    this = expressions[0]
3961    if expressions[1:]:
3962        this = _wrap_operator(this)
3963    for expression in expressions[1:]:
3964        this = operator(this=this, expression=_wrap_operator(expression))
3965    return this
3966
3967
3968def _wrap_operator(expression):
3969    if isinstance(expression, (And, Or, Not)):
3970        expression = Paren(this=expression)
3971    return expression
3972
3973
3974def union(left, right, distinct=True, dialect=None, **opts):
3975    """
3976    Initializes a syntax tree from one UNION expression.
3977
3978    Example:
3979        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
3980        'SELECT * FROM foo UNION SELECT * FROM bla'
3981
3982    Args:
3983        left (str | Expression): the SQL code string corresponding to the left-hand side.
3984            If an `Expression` instance is passed, it will be used as-is.
3985        right (str | Expression): the SQL code string corresponding to the right-hand side.
3986            If an `Expression` instance is passed, it will be used as-is.
3987        distinct (bool): set the DISTINCT flag if and only if this is true.
3988        dialect (str): the dialect used to parse the input expression.
3989        opts (kwargs): other options to use to parse the input expressions.
3990    Returns:
3991        Union: the syntax tree for the UNION expression.
3992    """
3993    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
3994    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
3995
3996    return Union(this=left, expression=right, distinct=distinct)
3997
3998
3999def intersect(left, right, distinct=True, dialect=None, **opts):
4000    """
4001    Initializes a syntax tree from one INTERSECT expression.
4002
4003    Example:
4004        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
4005        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
4006
4007    Args:
4008        left (str | Expression): the SQL code string corresponding to the left-hand side.
4009            If an `Expression` instance is passed, it will be used as-is.
4010        right (str | Expression): the SQL code string corresponding to the right-hand side.
4011            If an `Expression` instance is passed, it will be used as-is.
4012        distinct (bool): set the DISTINCT flag if and only if this is true.
4013        dialect (str): the dialect used to parse the input expression.
4014        opts (kwargs): other options to use to parse the input expressions.
4015    Returns:
4016        Intersect: the syntax tree for the INTERSECT expression.
4017    """
4018    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4019    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4020
4021    return Intersect(this=left, expression=right, distinct=distinct)
4022
4023
4024def except_(left, right, distinct=True, dialect=None, **opts):
4025    """
4026    Initializes a syntax tree from one EXCEPT expression.
4027
4028    Example:
4029        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
4030        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
4031
4032    Args:
4033        left (str | Expression): the SQL code string corresponding to the left-hand side.
4034            If an `Expression` instance is passed, it will be used as-is.
4035        right (str | Expression): the SQL code string corresponding to the right-hand side.
4036            If an `Expression` instance is passed, it will be used as-is.
4037        distinct (bool): set the DISTINCT flag if and only if this is true.
4038        dialect (str): the dialect used to parse the input expression.
4039        opts (kwargs): other options to use to parse the input expressions.
4040    Returns:
4041        Except: the syntax tree for the EXCEPT statement.
4042    """
4043    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4044    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4045
4046    return Except(this=left, expression=right, distinct=distinct)
4047
4048
4049def select(*expressions: str | Expression, dialect: DialectType = None, **opts) -> Select:
4050    """
4051    Initializes a syntax tree from one or multiple SELECT expressions.
4052
4053    Example:
4054        >>> select("col1", "col2").from_("tbl").sql()
4055        'SELECT col1, col2 FROM tbl'
4056
4057    Args:
4058        *expressions: the SQL code string to parse as the expressions of a
4059            SELECT statement. If an Expression instance is passed, this is used as-is.
4060        dialect: the dialect used to parse the input expressions (in the case that an
4061            input expression is a SQL string).
4062        **opts: other options to use to parse the input expressions (again, in the case
4063            that an input expression is a SQL string).
4064
4065    Returns:
4066        Select: the syntax tree for the SELECT statement.
4067    """
4068    return Select().select(*expressions, dialect=dialect, **opts)
4069
4070
4071def from_(*expressions, dialect=None, **opts) -> Select:
4072    """
4073    Initializes a syntax tree from a FROM expression.
4074
4075    Example:
4076        >>> from_("tbl").select("col1", "col2").sql()
4077        'SELECT col1, col2 FROM tbl'
4078
4079    Args:
4080        *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a
4081            SELECT statement. If an Expression instance is passed, this is used as-is.
4082        dialect (str): the dialect used to parse the input expression (in the case that the
4083            input expression is a SQL string).
4084        **opts: other options to use to parse the input expressions (again, in the case
4085            that the input expression is a SQL string).
4086
4087    Returns:
4088        Select: the syntax tree for the SELECT statement.
4089    """
4090    return Select().from_(*expressions, dialect=dialect, **opts)
4091
4092
4093def update(table, properties, where=None, from_=None, dialect=None, **opts) -> Update:
4094    """
4095    Creates an update statement.
4096
4097    Example:
4098        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
4099        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
4100
4101    Args:
4102        *properties (Dict[str, Any]): dictionary of properties to set which are
4103            auto converted to sql objects eg None -> NULL
4104        where (str): sql conditional parsed into a WHERE statement
4105        from_ (str): sql statement parsed into a FROM statement
4106        dialect (str): the dialect used to parse the input expressions.
4107        **opts: other options to use to parse the input expressions.
4108
4109    Returns:
4110        Update: the syntax tree for the UPDATE statement.
4111    """
4112    update = Update(this=maybe_parse(table, into=Table, dialect=dialect))
4113    update.set(
4114        "expressions",
4115        [
4116            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
4117            for k, v in properties.items()
4118        ],
4119    )
4120    if from_:
4121        update.set(
4122            "from",
4123            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
4124        )
4125    if isinstance(where, Condition):
4126        where = Where(this=where)
4127    if where:
4128        update.set(
4129            "where",
4130            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4131        )
4132    return update
4133
4134
4135def delete(table, where=None, dialect=None, **opts) -> Delete:
4136    """
4137    Builds a delete statement.
4138
4139    Example:
4140        >>> delete("my_table", where="id > 1").sql()
4141        'DELETE FROM my_table WHERE id > 1'
4142
4143    Args:
4144        where (str|Condition): sql conditional parsed into a WHERE statement
4145        dialect (str): the dialect used to parse the input expressions.
4146        **opts: other options to use to parse the input expressions.
4147
4148    Returns:
4149        Delete: the syntax tree for the DELETE statement.
4150    """
4151    return Delete(
4152        this=maybe_parse(table, into=Table, dialect=dialect, **opts),
4153        where=Where(this=where)
4154        if isinstance(where, Condition)
4155        else maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4156    )
4157
4158
4159def condition(expression, dialect=None, **opts) -> Condition:
4160    """
4161    Initialize a logical condition expression.
4162
4163    Example:
4164        >>> condition("x=1").sql()
4165        'x = 1'
4166
4167        This is helpful for composing larger logical syntax trees:
4168        >>> where = condition("x=1")
4169        >>> where = where.and_("y=1")
4170        >>> Select().from_("tbl").select("*").where(where).sql()
4171        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
4172
4173    Args:
4174        *expression (str | Expression): the SQL code string to parse.
4175            If an Expression instance is passed, this is used as-is.
4176        dialect (str): the dialect used to parse the input expression (in the case that the
4177            input expression is a SQL string).
4178        **opts: other options to use to parse the input expressions (again, in the case
4179            that the input expression is a SQL string).
4180
4181    Returns:
4182        Condition: the expression
4183    """
4184    return maybe_parse(  # type: ignore
4185        expression,
4186        into=Condition,
4187        dialect=dialect,
4188        **opts,
4189    )
4190
4191
4192def and_(*expressions, dialect=None, **opts) -> And:
4193    """
4194    Combine multiple conditions with an AND logical operator.
4195
4196    Example:
4197        >>> and_("x=1", and_("y=1", "z=1")).sql()
4198        'x = 1 AND (y = 1 AND z = 1)'
4199
4200    Args:
4201        *expressions (str | Expression): the SQL code strings to parse.
4202            If an Expression instance is passed, this is used as-is.
4203        dialect (str): the dialect used to parse the input expression.
4204        **opts: other options to use to parse the input expressions.
4205
4206    Returns:
4207        And: the new condition
4208    """
4209    return _combine(expressions, And, dialect, **opts)
4210
4211
4212def or_(*expressions, dialect=None, **opts) -> Or:
4213    """
4214    Combine multiple conditions with an OR logical operator.
4215
4216    Example:
4217        >>> or_("x=1", or_("y=1", "z=1")).sql()
4218        'x = 1 OR (y = 1 OR z = 1)'
4219
4220    Args:
4221        *expressions (str | Expression): the SQL code strings to parse.
4222            If an Expression instance is passed, this is used as-is.
4223        dialect (str): the dialect used to parse the input expression.
4224        **opts: other options to use to parse the input expressions.
4225
4226    Returns:
4227        Or: the new condition
4228    """
4229    return _combine(expressions, Or, dialect, **opts)
4230
4231
4232def not_(expression, dialect=None, **opts) -> Not:
4233    """
4234    Wrap a condition with a NOT operator.
4235
4236    Example:
4237        >>> not_("this_suit='black'").sql()
4238        "NOT this_suit = 'black'"
4239
4240    Args:
4241        expression (str | Expression): the SQL code strings to parse.
4242            If an Expression instance is passed, this is used as-is.
4243        dialect (str): the dialect used to parse the input expression.
4244        **opts: other options to use to parse the input expressions.
4245
4246    Returns:
4247        Not: the new condition
4248    """
4249    this = condition(
4250        expression,
4251        dialect=dialect,
4252        **opts,
4253    )
4254    return Not(this=_wrap_operator(this))
4255
4256
4257def paren(expression) -> Paren:
4258    return Paren(this=expression)
4259
4260
4261SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
4262
4263
4264@t.overload
4265def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None:
4266    ...
4267
4268
4269@t.overload
4270def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier:
4271    ...
4272
4273
4274def to_identifier(name, quoted=None):
4275    """Builds an identifier.
4276
4277    Args:
4278        name: The name to turn into an identifier.
4279        quoted: Whether or not force quote the identifier.
4280
4281    Returns:
4282        The identifier ast node.
4283    """
4284
4285    if name is None:
4286        return None
4287
4288    if isinstance(name, Identifier):
4289        identifier = name
4290    elif isinstance(name, str):
4291        identifier = Identifier(
4292            this=name,
4293            quoted=not re.match(SAFE_IDENTIFIER_RE, name) if quoted is None else quoted,
4294        )
4295    else:
4296        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
4297    return identifier
4298
4299
4300INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
4301
4302
4303def to_interval(interval: str | Literal) -> Interval:
4304    """Builds an interval expression from a string like '1 day' or '5 months'."""
4305    if isinstance(interval, Literal):
4306        if not interval.is_string:
4307            raise ValueError("Invalid interval string.")
4308
4309        interval = interval.this
4310
4311    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
4312
4313    if not interval_parts:
4314        raise ValueError("Invalid interval string.")
4315
4316    return Interval(
4317        this=Literal.string(interval_parts.group(1)),
4318        unit=Var(this=interval_parts.group(2)),
4319    )
4320
4321
4322@t.overload
4323def to_table(sql_path: str | Table, **kwargs) -> Table:
4324    ...
4325
4326
4327@t.overload
4328def to_table(sql_path: None, **kwargs) -> None:
4329    ...
4330
4331
4332def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]:
4333    """
4334    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
4335    If a table is passed in then that table is returned.
4336
4337    Args:
4338        sql_path: a `[catalog].[schema].[table]` string.
4339
4340    Returns:
4341        A table expression.
4342    """
4343    if sql_path is None or isinstance(sql_path, Table):
4344        return sql_path
4345    if not isinstance(sql_path, str):
4346        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
4347
4348    catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3))
4349    return Table(this=table_name, db=db, catalog=catalog, **kwargs)
4350
4351
4352def to_column(sql_path: str | Column, **kwargs) -> Column:
4353    """
4354    Create a column from a `[table].[column]` sql path. Schema is optional.
4355
4356    If a column is passed in then that column is returned.
4357
4358    Args:
4359        sql_path: `[table].[column]` string
4360    Returns:
4361        Table: A column expression
4362    """
4363    if sql_path is None or isinstance(sql_path, Column):
4364        return sql_path
4365    if not isinstance(sql_path, str):
4366        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
4367    table_name, column_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 2))
4368    return Column(this=column_name, table=table_name, **kwargs)
4369
4370
4371def alias_(
4372    expression: str | Expression,
4373    alias: str | Identifier,
4374    table: bool | t.Sequence[str | Identifier] = False,
4375    quoted: t.Optional[bool] = None,
4376    dialect: DialectType = None,
4377    **opts,
4378):
4379    """Create an Alias expression.
4380
4381    Example:
4382        >>> alias_('foo', 'bar').sql()
4383        'foo AS bar'
4384
4385        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
4386        '(SELECT 1, 2) AS bar(a, b)'
4387
4388    Args:
4389        expression: the SQL code strings to parse.
4390            If an Expression instance is passed, this is used as-is.
4391        alias: the alias name to use. If the name has
4392            special characters it is quoted.
4393        table: Whether or not to create a table alias, can also be a list of columns.
4394        quoted: whether or not to quote the alias
4395        dialect: the dialect used to parse the input expression.
4396        **opts: other options to use to parse the input expressions.
4397
4398    Returns:
4399        Alias: the aliased expression
4400    """
4401    exp = maybe_parse(expression, dialect=dialect, **opts)
4402    alias = to_identifier(alias, quoted=quoted)
4403
4404    if table:
4405        table_alias = TableAlias(this=alias)
4406        exp.set("alias", table_alias)
4407
4408        if not isinstance(table, bool):
4409            for column in table:
4410                table_alias.append("columns", to_identifier(column, quoted=quoted))
4411
4412        return exp
4413
4414    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
4415    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
4416    # for the complete Window expression.
4417    #
4418    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
4419
4420    if "alias" in exp.arg_types and not isinstance(exp, Window):
4421        exp = exp.copy()
4422        exp.set("alias", alias)
4423        return exp
4424    return Alias(this=exp, alias=alias)
4425
4426
4427def subquery(expression, alias=None, dialect=None, **opts):
4428    """
4429    Build a subquery expression.
4430
4431    Example:
4432        >>> subquery('select x from tbl', 'bar').select('x').sql()
4433        'SELECT x FROM (SELECT x FROM tbl) AS bar'
4434
4435    Args:
4436        expression (str | Expression): the SQL code strings to parse.
4437            If an Expression instance is passed, this is used as-is.
4438        alias (str | Expression): the alias name to use.
4439        dialect (str): the dialect used to parse the input expression.
4440        **opts: other options to use to parse the input expressions.
4441
4442    Returns:
4443        Select: a new select with the subquery expression included
4444    """
4445
4446    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
4447    return Select().from_(expression, dialect=dialect, **opts)
4448
4449
4450def column(
4451    col: str | Identifier,
4452    table: t.Optional[str | Identifier] = None,
4453    schema: t.Optional[str | Identifier] = None,
4454    quoted: t.Optional[bool] = None,
4455) -> Column:
4456    """
4457    Build a Column.
4458
4459    Args:
4460        col: column name
4461        table: table name
4462        schema: schema name
4463        quoted: whether or not to force quote each part
4464    Returns:
4465        Column: column instance
4466    """
4467    return Column(
4468        this=to_identifier(col, quoted=quoted),
4469        table=to_identifier(table, quoted=quoted),
4470        schema=to_identifier(schema, quoted=quoted),
4471    )
4472
4473
4474def cast(expression: str | Expression, to: str | DataType | DataType.Type, **opts) -> Cast:
4475    """Cast an expression to a data type.
4476
4477    Example:
4478        >>> cast('x + 1', 'int').sql()
4479        'CAST(x + 1 AS INT)'
4480
4481    Args:
4482        expression: The expression to cast.
4483        to: The datatype to cast to.
4484
4485    Returns:
4486        A cast node.
4487    """
4488    expression = maybe_parse(expression, **opts)
4489    return Cast(this=expression, to=DataType.build(to, **opts))
4490
4491
4492def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table:
4493    """Build a Table.
4494
4495    Args:
4496        table (str | Expression): column name
4497        db (str | Expression): db name
4498        catalog (str | Expression): catalog name
4499
4500    Returns:
4501        Table: table instance
4502    """
4503    return Table(
4504        this=to_identifier(table, quoted=quoted),
4505        db=to_identifier(db, quoted=quoted),
4506        catalog=to_identifier(catalog, quoted=quoted),
4507        alias=TableAlias(this=to_identifier(alias)) if alias else None,
4508    )
4509
4510
4511def values(
4512    values: t.Iterable[t.Tuple[t.Any, ...]],
4513    alias: t.Optional[str] = None,
4514    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
4515) -> Values:
4516    """Build VALUES statement.
4517
4518    Example:
4519        >>> values([(1, '2')]).sql()
4520        "VALUES (1, '2')"
4521
4522    Args:
4523        values: values statements that will be converted to SQL
4524        alias: optional alias
4525        columns: Optional list of ordered column names or ordered dictionary of column names to types.
4526         If either are provided then an alias is also required.
4527         If a dictionary is provided then the first column of the values will be casted to the expected type
4528         in order to help with type inference.
4529
4530    Returns:
4531        Values: the Values expression object
4532    """
4533    if columns and not alias:
4534        raise ValueError("Alias is required when providing columns")
4535    table_alias = (
4536        TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
4537        if columns
4538        else TableAlias(this=to_identifier(alias) if alias else None)
4539    )
4540    expressions = [convert(tup) for tup in values]
4541    if columns and isinstance(columns, dict):
4542        types = list(columns.values())
4543        expressions[0].set(
4544            "expressions",
4545            [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)],
4546        )
4547    return Values(
4548        expressions=expressions,
4549        alias=table_alias,
4550    )
4551
4552
4553def var(name: t.Optional[str | Expression]) -> Var:
4554    """Build a SQL variable.
4555
4556    Example:
4557        >>> repr(var('x'))
4558        '(VAR this: x)'
4559
4560        >>> repr(var(column('x', table='y')))
4561        '(VAR this: x)'
4562
4563    Args:
4564        name: The name of the var or an expression who's name will become the var.
4565
4566    Returns:
4567        The new variable node.
4568    """
4569    if not name:
4570        raise ValueError(f"Cannot convert empty name into var.")
4571
4572    if isinstance(name, Expression):
4573        name = name.name
4574    return Var(this=name)
4575
4576
4577def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
4578    """Build ALTER TABLE... RENAME... expression
4579
4580    Args:
4581        old_name: The old name of the table
4582        new_name: The new name of the table
4583
4584    Returns:
4585        Alter table expression
4586    """
4587    old_table = to_table(old_name)
4588    new_table = to_table(new_name)
4589    return AlterTable(
4590        this=old_table,
4591        actions=[
4592            RenameTable(this=new_table),
4593        ],
4594    )
4595
4596
4597def convert(value) -> Expression:
4598    """Convert a python value into an expression object.
4599
4600    Raises an error if a conversion is not possible.
4601
4602    Args:
4603        value (Any): a python object
4604
4605    Returns:
4606        Expression: the equivalent expression object
4607    """
4608    if isinstance(value, Expression):
4609        return value
4610    if value is None:
4611        return NULL
4612    if isinstance(value, bool):
4613        return Boolean(this=value)
4614    if isinstance(value, str):
4615        return Literal.string(value)
4616    if isinstance(value, float) and math.isnan(value):
4617        return NULL
4618    if isinstance(value, numbers.Number):
4619        return Literal.number(value)
4620    if isinstance(value, tuple):
4621        return Tuple(expressions=[convert(v) for v in value])
4622    if isinstance(value, list):
4623        return Array(expressions=[convert(v) for v in value])
4624    if isinstance(value, dict):
4625        return Map(
4626            keys=[convert(k) for k in value],
4627            values=[convert(v) for v in value.values()],
4628        )
4629    if isinstance(value, datetime.datetime):
4630        datetime_literal = Literal.string(
4631            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
4632        )
4633        return TimeStrToTime(this=datetime_literal)
4634    if isinstance(value, datetime.date):
4635        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
4636        return DateStrToDate(this=date_literal)
4637    raise ValueError(f"Cannot convert {value}")
4638
4639
4640def replace_children(expression, fun):
4641    """
4642    Replace children of an expression with the result of a lambda fun(child) -> exp.
4643    """
4644    for k, v in expression.args.items():
4645        is_list_arg = isinstance(v, list)
4646
4647        child_nodes = v if is_list_arg else [v]
4648        new_child_nodes = []
4649
4650        for cn in child_nodes:
4651            if isinstance(cn, Expression):
4652                for child_node in ensure_collection(fun(cn)):
4653                    new_child_nodes.append(child_node)
4654                    child_node.parent = expression
4655                    child_node.arg_key = k
4656            else:
4657                new_child_nodes.append(cn)
4658
4659        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
4660
4661
4662def column_table_names(expression):
4663    """
4664    Return all table names referenced through columns in an expression.
4665
4666    Example:
4667        >>> import sqlglot
4668        >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e"))
4669        ['c', 'a']
4670
4671    Args:
4672        expression (sqlglot.Expression): expression to find table names
4673
4674    Returns:
4675        list: A list of unique names
4676    """
4677    return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
4678
4679
4680def table_name(table) -> str:
4681    """Get the full name of a table as a string.
4682
4683    Args:
4684        table (exp.Table | str): table expression node or string.
4685
4686    Examples:
4687        >>> from sqlglot import exp, parse_one
4688        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
4689        'a.b.c'
4690
4691    Returns:
4692        The table name.
4693    """
4694
4695    table = maybe_parse(table, into=Table)
4696
4697    if not table:
4698        raise ValueError(f"Cannot parse {table}")
4699
4700    return ".".join(
4701        part
4702        for part in (
4703            table.text("catalog"),
4704            table.text("db"),
4705            table.name,
4706        )
4707        if part
4708    )
4709
4710
4711def replace_tables(expression, mapping):
4712    """Replace all tables in expression according to the mapping.
4713
4714    Args:
4715        expression (sqlglot.Expression): expression node to be transformed and replaced.
4716        mapping (Dict[str, str]): mapping of table names.
4717
4718    Examples:
4719        >>> from sqlglot import exp, parse_one
4720        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
4721        'SELECT * FROM c'
4722
4723    Returns:
4724        The mapped expression.
4725    """
4726
4727    def _replace_tables(node):
4728        if isinstance(node, Table):
4729            new_name = mapping.get(table_name(node))
4730            if new_name:
4731                return to_table(
4732                    new_name,
4733                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
4734                )
4735        return node
4736
4737    return expression.transform(_replace_tables)
4738
4739
4740def replace_placeholders(expression, *args, **kwargs):
4741    """Replace placeholders in an expression.
4742
4743    Args:
4744        expression (sqlglot.Expression): expression node to be transformed and replaced.
4745        args: positional names that will substitute unnamed placeholders in the given order.
4746        kwargs: keyword arguments that will substitute named placeholders.
4747
4748    Examples:
4749        >>> from sqlglot import exp, parse_one
4750        >>> replace_placeholders(
4751        ...     parse_one("select * from :tbl where ? = ?"), "a", "b", tbl="foo"
4752        ... ).sql()
4753        'SELECT * FROM foo WHERE a = b'
4754
4755    Returns:
4756        The mapped expression.
4757    """
4758
4759    def _replace_placeholders(node, args, **kwargs):
4760        if isinstance(node, Placeholder):
4761            if node.name:
4762                new_name = kwargs.get(node.name)
4763                if new_name:
4764                    return to_identifier(new_name)
4765            else:
4766                try:
4767                    return to_identifier(next(args))
4768                except StopIteration:
4769                    pass
4770        return node
4771
4772    return expression.transform(_replace_placeholders, iter(args), **kwargs)
4773
4774
4775def expand(expression: Expression, sources: t.Dict[str, Subqueryable], copy=True) -> Expression:
4776    """Transforms an expression by expanding all referenced sources into subqueries.
4777
4778    Examples:
4779        >>> from sqlglot import parse_one
4780        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
4781        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
4782
4783    Args:
4784        expression: The expression to expand.
4785        sources: A dictionary of name to Subqueryables.
4786        copy: Whether or not to copy the expression during transformation. Defaults to True.
4787
4788    Returns:
4789        The transformed expression.
4790    """
4791
4792    def _expand(node: Expression):
4793        if isinstance(node, Table):
4794            name = table_name(node)
4795            source = sources.get(name)
4796            if source:
4797                subquery = source.subquery(node.alias or name)
4798                subquery.comments = [f"source: {name}"]
4799                return subquery
4800        return node
4801
4802    return expression.transform(_expand, copy=copy)
4803
4804
4805def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
4806    """
4807    Returns a Func expression.
4808
4809    Examples:
4810        >>> func("abs", 5).sql()
4811        'ABS(5)'
4812
4813        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
4814        'CAST(5 AS DOUBLE)'
4815
4816    Args:
4817        name: the name of the function to build.
4818        args: the args used to instantiate the function of interest.
4819        dialect: the source dialect.
4820        kwargs: the kwargs used to instantiate the function of interest.
4821
4822    Note:
4823        The arguments `args` and `kwargs` are mutually exclusive.
4824
4825    Returns:
4826        An instance of the function of interest, or an anonymous function, if `name` doesn't
4827        correspond to an existing `sqlglot.expressions.Func` class.
4828    """
4829    if args and kwargs:
4830        raise ValueError("Can't use both args and kwargs to instantiate a function.")
4831
4832    from sqlglot.dialects.dialect import Dialect
4833
4834    args = tuple(convert(arg) for arg in args)
4835    kwargs = {key: convert(value) for key, value in kwargs.items()}
4836
4837    parser = Dialect.get_or_raise(dialect)().parser()
4838    from_args_list = parser.FUNCTIONS.get(name.upper())
4839
4840    if from_args_list:
4841        function = from_args_list(args) if args else from_args_list.__self__(**kwargs)  # type: ignore
4842    else:
4843        kwargs = kwargs or {"expressions": args}
4844        function = Anonymous(this=name, **kwargs)
4845
4846    for error_message in function.error_messages(args):
4847        raise ValueError(error_message)
4848
4849    return function
4850
4851
4852def true():
4853    """
4854    Returns a true Boolean expression.
4855    """
4856    return Boolean(this=True)
4857
4858
4859def false():
4860    """
4861    Returns a false Boolean expression.
4862    """
4863    return Boolean(this=False)
4864
4865
4866def null():
4867    """
4868    Returns a Null expression.
4869    """
4870    return Null()
4871
4872
4873# TODO: deprecate this
4874TRUE = Boolean(this=True)
4875FALSE = Boolean(this=False)
4876NULL = Null()